Alternative to vlookup – Index and Match

Lookup from Right to Left


In the above example, if it is given the Student ID, it would be so easy to find one’s corresponding Name and Grade by using vlookup.  However, if we would need to find out the Student ID from one’s name, vlookup fails to help… 😦

I hope my previous posts help you understand vlookup a bit more.  You may be aware of a drawback of vlookup: the direction of the lookup.  It ALWAYS lookups the value from the LEFTMOST column in the table, then return the corresponding value X column(s) to the right.  vlookup CANNOT return a value that is on the LEFT of the lookup value.

(Yes, actually you may do left hand vlookup with CHOOSE function which many websites have described it.  However, I find that too complicated for majority users.  Moreover, I prefer simplicity to difficulty! Index and Match is definitely a better option comparing to vlookup and Choose.)

For this example, we know that Flora is the 6th value under Student Name. Her Student ID then the 6th (corresponding) value under Student ID.  In other words, we need a formula to get the 6th value under Student ID.  Here we come the INDEX function.

INDEX Functions 

There are actually two INDEX functions; one is array, the other one is reference.



The more I learn about INDEX, the more I am fond of it. 🙂 Indeed, if you can master INDEX, you will be an Excel guru to most ordinary users.

Nevertheless, for the sake of using INDEX together with Match as an alternative to vlookup, we can start with the basic of INDEX:


Let’s confine the array in one row or column only.  In this way, we need to input only 2 arguments for the INDEX which is really easy to understand:


For Example:
=Index($A$2:$A$11,6) simply asks Excel to return the 6th value in the array of A2:A11

In our example, the result is “Flora”.

Therefore, if we know the relative position of “Flora” under Student Name (which is 6), then we should be able to get the corresponding Student ID of Flora easily.

Now you may have the question: How do I know the relative position of Flora?

Still remember the Match function we discussed before?

By using Match

=Match($E6,$B$2:$B$11,0) where

  • E6 is the lookup value, i.e. “Flora”
  • B2:B11 is the array that holds the Student Name
  • 0 means Exact Match.

By substituting this Match Function into the Index Function, we will get:

=Index($A$2:$A$11, Match($E6,$B$2:$B$11,0))

The result will be “P006” which is the Student ID of Flora.  See it is exactly a lookup from right to left!


Other topics about vlookup:

  1. The basic of vlookup
  2. Tips in constructing vlookup
  3. vlookup with Match 
  4. vlookup options – True or False?
  5. Advanced vlookup – Text vs. Number
  6. Advanced vlookup – Wildcard Characters “?” and “*”
  7. Alternative to vlookup – Index and Match
  8. Three different ways to do case-sensitive lookup
This entry was posted in Formula and tagged , , . Bookmark the permalink.

3 Responses to Alternative to vlookup – Index and Match

  1. Leilani says:

    I created an Excel spreadsheet using vlookup which gets information from another sheet within the same spreadsheet. Everything was working well after the initial setup. Now it is time to update for the new year and I cannot get the formulas to work properly. I have even tried to use the index-match alternative to no avail. This is my formula
    =VLOOKUP(N5&O5,CHOOSE({1,2},Sheet2!B$4:B$1133&Sheet2!C$4:C$1133,Sheet2!F$4:F$1133),5,0) which results in a #N/A. Any suggestions?


  2. Pingback: Vlookup Question

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s