Backwards VLOOKUP: A Quick Guide with Examples

The VLOOKUP function is very powerful. However, it is limited to only referencing data that is in the leftmost column of the table array. This tutorial will step through a simple alternative to VLOOKUP which allows for a reverse lookup

Final Formula

=INDEX(A3:B8,MATCH(E2,B3:B8,0),1)

Figure 1. Backwards VLOOKUP

Setting up the Data

First, we set our data up so that the value to be returned is in Column A and the lookup array is in Column B. Then, we will put the lookup value in cell E2 and we will use cell E3 as our formula cell where we want the data to be returned

• In this example, we will lookup the student’s age based on his/her name

Figure 2. Backwards VLOOKUP

Now, we have our data setup, but before we will get into the formula, we will first breakdown the different components of the formula

INDEX MATCH Method

The INDEX and MATCH functions our two individual functions that on their own, aren’t very powerful. We will first walk through each function and then we will merge them together in order to create a super lookup function

• The INDEX function allows a user to specify where in table he/she would like to return data. To do this, he/she must specify where the table is located and the ROW and COLUMN within the table that he/she wants to return
• INDEX Syntax: =Index(Table Array, ROW, COLUMN)
• In cell E2, type this formula “=INDEX(A3:B8,3,2)”

Figure 3. Backwards VLOOKUP

In this Example we see that the formula returns “Matt” because his name is located in the third row and second column of the table

• The MATCH function allows a user to return the ROW within a table array where a match to the lookup value is found
• MATCH Syntax = MATCH(Lookup Value, Table Array, Approximate Match [True/False])
• In cell E3, type this formula “=MATCH(E2,B3:B8,FALSE)”

Figure 4. Backwards VLOOKUP

In this Example we see that the formula returns “3” because ROW 3 is where the matching value to cell E2 is in the lookup array. Next, we will merge the INDEX and MATCH functions to complete the process

Putting it all Together

We can now use the INDEX function to tell Excel where to lookup the value and the MATCH function to tell the INDEX function which row to find the lookup value in the lookup array To do this, we nest the MATCH formula from the above example inside the INDEX formula from the earlier example. However, we change the column to “1 “instead of “2” since we want to return the age of the student

• In cell E3 type this formula “=INDEX(A3:B8,MATCH(E2,B3:B8,FALSE),1)”

Figure 5. Backwards VLOOKUP

We can see in this Example that the formula in cell E3 returns “14” because “Matt” is located in the third ROW of the table array and his age is “14”

Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.