• Skip to main content

Annielytics.com

I make data sexy

  • About
  • Services
  • Tools
  • Blog
  • Portfolio
  • Contact
  • Log In

Jun 27 2014

Excel’s INDEX MATCH Alternative To VLOOKUP For Marketers [VIDEO]

woman in blue denim jeans lying on black floor
Now that’s flexible!

Last week’s #FunctionFriday video tutorial covered VLOOKUP tips and techniques for marketers. This week’s video explores the INDEX MATCH alternative to VLOOKUP.

Why would VLOOKUP need an understudy? Because VLOOKUPs require that the lookup column be the far-left column in your lookup data set (aka the table_array argument). The INDEX-MATCH dream team doesn’t have that requirement, making it more flexible.

Video

Comparing VLOOKUP and MATCH

I make a comment in the video about how the MATCH function is nearly identical to the VLOOKUP function and wanted to expand on that here.

Here’s the syntax for VLOOKUP:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

And here’s the syntax for MATCH:

MATCH(lookup_value, lookup_array, [match_type])

For marketing purposes, they’re practically fraternal twins. But there are a couple key differences:

  • With VLOOKUP, you’re working with a table, so you need the col_index_num argument. With MATCH you’re just feeding it one column or row.
  • Much like the INDEX function, VLOOKUP returns the value in the cell that meets the lookup requirements; MATCH returns the relative position of the cell where it finds a match to your lookup value.

Note: Since marketers are most commonly working with exact match for both of these functions and you can use 0 in lieu of FALSE in a VLOOKUP, the range_lookup and match_type arguments accomplish identical objectives: telling Excel to only look for exact matches.

Excel Download

You can download the file I used in the video tutorial here.

Image Credit: Oksana Taran on Unsplash

Written by Annie Cushing · Categorized: Excel

Reader Interactions

Comments

  1. Whitney Eden says

    June 27, 2014 at 1:32 pm

    The link to download the file seems to be broken

    Reply
    • Annie Cushing says

      June 27, 2014 at 1:34 pm

      It’s working for me. It’s a Dropbox link.

      Reply
      • Whitney Eden says

        June 27, 2014 at 2:01 pm

        Ah, gotcha. That’s blocked at work. Thanks!

        Reply
        • Annie Cushing says

          June 27, 2014 at 2:05 pm

          Humph. Here you go! https://annielytics.com/?attachment_id=2332

          Reply
          • Whitney Eden says

            June 27, 2014 at 2:07 pm

            Awesome. Thanks!

          • Annie Cushing says

            June 27, 2014 at 2:53 pm

            You’re welcome!

  2. Philip Tomlinson says

    August 14, 2014 at 4:49 pm

    It’s also possible to use INDEX MATCH to act like VLOOKUP when you’re trying to use two values as criterias!

    =INDEX(table with value you want,MATCH(1,(ColumnA=Criteria1)*(ColumnB=Criteria2),0),column with the data you want)

    All you need to do is CTRL-ALT-ENTER when entering the function.

    Reply
    • Annie Cushing says

      August 15, 2014 at 4:25 pm

      I’d love to see an actual example of this – perhaps a screenshot or uploaded workbook?

      Reply
  3. Megan says

    March 14, 2015 at 12:49 pm

    Hi –

    I’m working on a project where I need to use the MATCH function, but I need to consider that the content I’m looking for is not an exact match. For example, I pulled a list of companies from Salesforce, and we want to match those against internal records. Since company names are not always entered in properly in SFDC, or don’t have the “Inc.” or “Ltd.” included, for example, I need flexibility to search for part or variations of the company name. What do you suggest I use for this formula?

    Thanks!

    Reply
    • Annie Cushing says

      March 16, 2015 at 8:43 am

      You can use wildcards to get a partial match. I use them a lot with SEARCH functions, but you can use them with any text-lookup function in Excel.

      Reply
  4. Tommy says

    July 29, 2016 at 11:54 am

    Hi Annie. This video is awesome.

    I just had one question…can you explain the COLUMN() function you use at the end of the INDEX function? How would this work if I didn’t have an empty far right column?

    Thanks!

    Reply
    • Annie Cushing says

      July 31, 2016 at 7:16 pm

      The COLUMN function just enables the formula to update dynamically so that you don’t have to manually write the formula for each column. I explain how to use it in the video on this page as well as the video on this page: https://annielytics.com/blog/excel-tips/vlookup-tips-tricks-marketers/.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Copyright © 2025