Alternative to VLOOKUP: INDEX & MATCH

VLOOKUP and HLOOKUP take up a lot of memory and processing power, especially if you have a large data set. A great alternative to VLOOKUPs and HLOOKUPs are the INDEX & MATCH functions. Below is an example from Mr. Excel.

The Problem

Retrieve Data

One day, you have a situation where you have the employee name, but need the employee number. In the image above, you have a name in A10 and need to find the employee number in B10.

When the key field is to the right of the data you want to retrieve, VLOOKUP will not work. If only the VLOOKUP would accept -1 as the column number, there would be no problem. But, it doesn’t. One common solution is to temporarily insert a new column A, copy the column of names to the new column A, populate with VLOOKUP, Paste Special Values, then delete the temporary column A. Excel pros can probably do this move in their sleep.

I am going to suggest you take the challenge and try to use this single step method. Yes, you will have to tack the formula up on your wall for a few weeks, but you did that with VLOOKUP a long time ago, too, didn’t you?

I think the reason this is so difficult is that you are using two functions which you probably never used before. So, let me break it down into two pieces.

The Solution: INDEX & MATCH

INDEX Function

First, there is the INDEX() function. This is a horribly named function. When someone says “index”, it does not conjure up anything in my mind that is similar to what this function does. Index requires three arguments.

=INDEX(data range, row number, column number)

In English, Excel goes to the data range and returns you the value in the intersection of the (row number)th row and the (column number)th column. Hey, think about it – this is pretty simple, right? =INDEX($A$2:$C$6,4,2) will give you the value in B5.

Applying INDEX() to our problem, you can figure that to return the employee number from the range, you would use this: =INDEX($A$2:$A$6,?,1). Actually, this piece of it seems so trivial that it seems useless. But, when you replace the question mark with a MATCH() function, you have the solution.

MATCH Function

Here is the syntax:

=MATCH(Value, Single-column data range, FALSE)

It tells Excel, “Search the data range and tell me the relative row number where you find a match for (data). So, to find which row has the employee in A10, you would use =MATCH(A10,$B$2:$B$6,FALSE). Yes, this is more complex than Index, but it should be right up the alley of VLOOKUP pros. If A10 contains “Miller, Bob” then this MATCH will return that he is in the 3rd row of the range B2:B6.

INDEX and MATCH Functions Together

There it is – the MATCH() function tells the Index function which row to look in – you are done. Take the Index function, replace our question mark with the MATCH function, and you can now do the equivalent of VLOOKUPs when the key field is not in the left column. Here is the function to use:

=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)

The sticky note on my wall actually shows it as two lines. First I wrote out the explanation for MATCH(). Below that I wrote the explanation for INDEX(). I then drew a funnel shape between the two to indicate that the MATCH() function drops in to the 2nd argument of the INDEX() function.

The first few times I had to do one of these, I was tempted just to slam a new temporary column A in there, but went through the pain of doing it this way instead. It is faster, and requires less manipulation. So, the next time you are wishing you could put a negative number in the VLOOKUP function, try this strange combination of INDEX and MATCH to solve your problems.

 

This entry was posted in Functions. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

38 Comments

  1. Zaf
    Posted January 22, 2015 at 4:41 pm | Permalink

    Hey Karen,
    When I use the Index/Match formula on a 3000 rows table it freezes my excel and has that % increasing over an hour, and eventually it calculates, but whenever I toutch the table it does it again…
    Can this be solved?

  2. Klas
    Posted December 4, 2014 at 7:01 am | Permalink

    Super !
    I had trouble getting VLOOKUP to work (For unknown reasons) but the INDEX+MATCH got the job done.

    VLOOKUP didnt find all the data for some reason…

    /K

    • karen
      Posted January 22, 2015 at 4:48 pm | Permalink

      Hi Klas! That’s strange. VLOOKUP and INDEX+MATCH should work in the exact same way! I am happy INDEX+MATCH worked for you though!

  3. mark danby
    Posted July 7, 2014 at 9:21 am | Permalink

    Hello,

    I need to do a vlookup from one column which then enters this data to another form (its a customs delaration for a vessel)
    I am using a persons onboard sheet as reference. Each person is allocated a cabin but not all beds are always used so there will be space in some cells.
    I only want the data entered for the beds used and need to bypass the empty beds.

    Basically on the declaration I want to omit blank lines so the declaration looks a little more professional.
    Thoughts appreciated..
    Best regards

    Mark

    • karen
      Posted January 22, 2015 at 5:25 pm | Permalink

      Hi Mark! That does sound like a problem that VLOOKUP or INDEX & MATCH can easily solve! Have you tried using the formula above?

  4. muthukumar
    Posted January 17, 2014 at 9:30 am | Permalink

    Hi,

    here you given formula for sinle row. but i need
    in one excel following
    Emp.# Name Dept
    a204 smith, jo Accounting
    a206 doe, jane Operations
    a208 miller, bob MID
    a210 white, fred Accounting
    a212 young, jill Manufacturing
    a212 young, jill Accounting

    in another
    smith, jo
    doe, jane
    miller, bob
    young, jill
    young, jill

    how to retrieve multiple result for young, jill.

    • karen
      Posted January 22, 2015 at 5:30 pm | Permalink

      Hi Muthukumar, you are right, VLOOKUP and INDEX & MATCH will only return the first result they find. In your example above, you have two instances of “young, jill”. VLOOKUP and INDEX & MATCH will only return the first result, which is “Manufacturing”. Do you have unique ID’s for each of these rows? Whenever you create a table, it’s always useful to have a unique identifier for each row. That way, even when there are duplicates, you can still easily do a match.

  5. Kevin
    Posted December 27, 2013 at 9:27 am | Permalink

    small typo – might confuse some newbies…

    you have a name in A10 and need to find the employee number in B10.

    • karen
      Posted January 22, 2015 at 5:05 pm | Permalink

      Hi Kevin, that’s exactly what I meant — if you have a name in A10 and you want to write a formula in B10 to return the employee number that matches the name in A10.

  6. Sholeye Femi
    Posted December 18, 2013 at 5:49 am | Permalink

    How do i pick a vertical data in one sheet into another sheet and to be horizontally arranged. That is, vertical to horizontal without the use of tranpose?

    • karen
      Posted December 19, 2013 at 12:19 pm | Permalink

      Hi Sholeye! You can use HLOOKUP instead of VLOOKUP. Have you tried that? “H” in HLOOKUP stands for horizontal and “V” in VLOOKUP stands for vertical 🙂

  7. Shihao
    Posted December 18, 2013 at 1:09 am | Permalink

    Hi, I would like to enquire about some issues regarding excel. I encountered some problems where I would have to extract information from four different database and then do V-Lookup to consolidate them into one spreadsheet. My team is trying to find an alternate solution which is less tedious. Do you have any suggestions regarding this issue? Thank you for your help!!!

    • karen
      Posted December 18, 2013 at 1:11 am | Permalink

      Hi Shihao! I understand that you want to consolidate information from four different tables into one. The easiest way to do to this would be to use either the VLOOKUP or the INDEX & MATCH formula described in this article. Have you tried these formulas?

  8. Fran
    Posted September 9, 2013 at 7:49 am | Permalink

    Is there any way to make the function only look up information on data that is entered. I have used the index/match in a row. So if first name and last name equal, specific information in entered from another worksheet to a column. Every time I do this now, it freezes my computer. Also, I will copy and paste some of the data to make reports, it tries to link the copied information to the original. I only want the function to work on the original sheet and not update my reports.
    Thanks

    • karen
      Posted September 9, 2013 at 11:49 am | Permalink

      Hi Fran! I want to make sure that I understood your question. I understand that you do NOT want your workbook to recalculate automatically because it causes your computer to freeze. Is that right? I have a tutorial here on how to make Excel stop calculating automatically: http://howtouseexcel.net/how-to-make-excel-stop-calculating-automatically Hope this helps, Fran!

      • Chris
        Posted September 27, 2013 at 10:45 am | Permalink

        If you are manually copy/pasting then why not try ‘Paste Special’ (found in the Right click menu) and the select ‘Values’. This just pastes the displayed values and not the formulas/links.

  9. Wendy
    Posted August 15, 2013 at 2:56 am | Permalink

    I am interested to know how I can use index and match for multiple criteria using formula.
    a) to identify for each cow time that moves On and Off each site (3, 4 or 5 as in example).
    I’ve given each record an identifier but may need to change this- she can revisit sites (as in the example). Also need to count the number of times the site is shown in the column i.e. 9 times for site 4 for cow A.

    Want to try and get something that I can download data into and then it self computes my data rather than doing this manually. What would be a good approach to use using avariety of formulae like index match, vlookup etc. Any help appreciated…thanks

    Cow identifier code unit timer
    A 1 On 0:00:03
    A 1 4 0:00:04
    A 1 Off 0:00:06
    A 2 On 0:00:09
    A 2 5 0:00:10
    A 2 5 0:00:15
    A 2 5 0:00:45
    A 2 5 0:01:45
    A 2 5 0:02:32
    A 2 Off 0:03:50
    A 3 On 0:03:52
    A 3 4 0:03:58
    A 3 4 0:04:04
    A 3 4 0:04:10
    A 3 4 0:04:15
    A 3 4 0:04:26
    A 3 4 0:04:34
    A 3 4 0:04:38
    A 3 4 0:04:45
    A 3 Off 0:05:00
    B 4 On 0:05:02
    B 4 4 0:05:03
    B 4 Off 0:05:10
    B 5 On 0:05:15
    B 5 3 0:05:45
    B 5 3 0:06:05
    B 5 Off 0:06:45

    • karen
      Posted August 15, 2013 at 9:49 am | Permalink

      Hi Wendy! Have you tried to use a combination of the CONCATENATE and the VLOOKUP (or INDEX & MATCH functions)? For example, you can concatenate the first two (or three) columns and then do a lookup afterwards.

  10. Posted August 7, 2013 at 12:35 pm | Permalink

    Very helpful function! I just used it to replace an ugly control table I was using to copy data from a pivot table to another table just to sequence it to perform a vlookup. While I can appreciate the limitations other mentioned this is far better than what I was doing.

    • karen
      Posted August 7, 2013 at 5:26 pm | Permalink

      Hi Marc! I am so happy to hear that I was able to help 🙂

  11. Alejandro
    Posted June 20, 2013 at 10:19 am | Permalink

    Thanks for the tip, worked perfect.
    I have used Vlookup/Hlookup for long and this Index/Match solves some of my concerns, mainly the sorting issue with said functions. I changed the “column number” argument in Index from “1” to a user variable so the Index/Match searches an entire table.

    • karen
      Posted June 20, 2013 at 10:32 am | Permalink

      Hi Alejandro! Glad to hear that the tip worked 🙂

  12. raj
    Posted June 2, 2013 at 10:25 am | Permalink

    Thanks a lot this was so much useful

    • karen
      Posted June 2, 2013 at 4:04 pm | Permalink

      You’re very welcome Raj! 🙂

  13. Tom B.
    Posted April 16, 2013 at 4:11 pm | Permalink

    Hi Karen – Can this work if my data is on one worksheet but my Index-Match equation is on a different worksheet? The equation seems to get confused when I try to specify the column number for the index function. It defaults to the column number of the worksheet where my equation resides not the column number of the worksheet where my data resides.

    • karen
      Posted April 19, 2013 at 11:02 am | Permalink

      Hi Tom! This formula definitely works across worksheets. Just make sure to reference the correct worksheet in the formula. The easiest way to do that is to just highlight the entire column you’re indexing on another worksheet. Let me know in case it doesn’t work!

  14. sulaiman
    Posted April 15, 2013 at 6:01 pm | Permalink

    That’s amazing. Another limitation is that we cannot retreive multiple values if they are lined up in same column against one reference. As vlookup gives first entry only. Is there any other function where I could get multiple entries against same reference?

    • karen
      Posted April 15, 2013 at 11:16 pm | Permalink

      I see what you mean! Unfortunately, even INDEX & MATCH also only returns the first entry. I don’t know of any Excel formula that can retrieve multiple values, but I know that you can use a SQL query to do that.

  15. GOKULDAS
    Posted March 11, 2013 at 10:46 am | Permalink

    WHAT CAN I DO TO LOOKUP WHERE THE LOOKUP VALUE REPEATS????

    • karen
      Posted June 20, 2013 at 10:48 am | Permalink

      Hi Gokuldas! What do you mean by the “LOOKUP value repeats”? Can you describe it in more detail, so I can help you?

  16. Antony
    Posted February 9, 2013 at 11:30 am | Permalink

    Thank you Karen, this little tip is very helpful!

    • karen
      Posted February 9, 2013 at 11:34 am | Permalink

      You’re welcome Antony! Glad to hear I was able to help 🙂

  17. Goobe
    Posted September 10, 2012 at 12:40 am | Permalink

    This is perfect!! The limitation of VLookUp is that you need to have columns in an order but with this you can reference to any column! 🙂

    • karen
      Posted September 10, 2012 at 8:32 am | Permalink

      Glad to hear you found INDEX & MATCH helpful. You are right, that’s another benefit in addition to the performance benefits of using this as an alternative to VLOOKUP.

  18. Apostolos55
    Posted July 5, 2012 at 4:31 pm | Permalink

    look for “GETIF() advanced” or “GETIF() v3.00”, in the latest form.
    It uses VBA. Read comments to uncover some possibilities.

    don’t misunderstand me, I do not say things cannot be done without this function, I am just saying that even novices can use GETIF() to get array of matches without need to become experts in Match,Index,…Lookup etc

    Usage in conjunction with offset() is uplifting

  19. Posted May 14, 2012 at 8:34 am | Permalink

    This is a wonderful tip. I was having a headache with Vlookup and a very large list. Your post saved my day. 🙂

    • karen
      Posted May 14, 2012 at 9:17 am | Permalink

      Glad to hear the tip was helpful!

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*