## 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.

Syntax

INDEX(array,row_num,column_num)
INDEX(reference,row_num,column_num,area_num)

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:

INDEX(array,row_num,column_num)

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!  An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
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?

Like

• MF says:

Hi Leilani
The column index should be 2, not 5. Kindly check and see. Remind you to press ctrl+shift +enter
Hope it works

Like

2. Pingback: Vlookup Question

This site uses Akismet to reduce spam. Learn how your comment data is processed.