Reverse VLOOKUP in Excel with Index-Match (Right to Left)

Last Updated: November 21, 2023
puneet-gogia-excel-champs

- Written by Puneet

Combine INDEX and MATCH for a Reverse VLOOKUP (Right to Left)

To understand the working of INDEX and MATCH as a reverse lookup formula, a simple thing you need to pick: Match tells the index the position (cell number) of a value in a column or in a row, and then, the index returns that value using that position (cell number).

Just think like this, the MATCH function is an undercover agent who finds the criminal and the INDEX function is a cop who arrests that criminal afterward.

index-match-getting-value

But, let’s learn in detail how we can combine these two functions. Below is the syntax of INDEX, as you know.

INDEX(array, row_num, [column_num])

In the INDEX function, the row_num argument tells it, that from which row it has to return the value. Let’s say if you enter 4 it will return the value from the 4th row.

To create the reverse lookup formula we need to replace MATCH with row_argument of INDEX.

When we use MATCH, it looks up the value from the lookup column and returns the cell number of that value. And then, INDEX uses that number to figure out the position of the cell from the value column.

In the end, it returns the value from that cell and you get the value you are looking for. But now, let’s work with a real example. Below we have a list of cities and names of the employees who are working there.

index match sample formula enter

Here we need to look up the employee name who’s working in Mumbai. Now, if look at the data, in the column where you have cities, that is our lookup column, and in the column where you have employees’ names, that is your value column. And, the formula will be:

=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)
index match sample formula enter

Let’s break down this formula into two parts to understand it.

Part 1: In part one, we used the match function to look up the value “Mumbai” and it returned the “5” which is the position of the cell in which you have the value “Mumbai” in the cities column.

index match how match works

Part 2: In part two, we used INDEX and referred to the employee’s name column for the lookup of the value. Here index function knows that you want the value from the 5th cell from the column. So, it has returned “Siya” in the result.

index match how index works

More Examples of INDEX and Match

Ahead we have some of the common problems which we have solved using the INDEX MATCH formula. Get Access to Sample Files: Make sure to download these sample files from here to follow along with each and every example.

1. Basic Lookup with INDEX – MATCH

A normal lookup is one of the most important tasks you need to do with lookup formulas and INDEX MATCH is perfect for this. Here we have a data table with the employee ID and name. Each ID is unique and you need to lookup up the employee’s name with his/her ID.

nomal lookup with index match data table

Let’s say you want to look up the name EMP-132. For this, the formula will be:

=INDEX(name_column,MATCH(emp-id,emp-id_column,0))

normal lookup with index match enter formula

here’s how this formula works

First of all, MATCH matches the emp id in the emp id column and returns the cell number of the id for which you are looking. Here row number is 6.

normal lookup with index match match give row number

After that, INDEX returns the employee name from the name column using the same cell number.

normal lookup with index match index returns name

2. Lookup to the Left

VLOOKUP can’t make it to the left while looking for a value. As I have mentioned, in INDEX and MATCH you can perform a lookup in any direction. In the below data table, you have the invoice number column after the amount column.

lookup to left with index match data table

So, if you want to look up the amount of any particular invoice, this is not possible with VLOOKUP. In VLOOKUP when you select a table, the first column in that table will be the lookup column.

But, here in this table, we need to use the last column of the table as a lookup column. So, thumb down for VLOOKUP here. Let’s call INDEX and MATCH for the rescue and the formula will be:

=INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

lookup to left with index match enter formula

…here’s how this formula works

  • First of all, you have referred to the amount column in the index function. This is the column from where we need to get the value.
  • Second, in the row_number argument of the index function, you have used the match function and specified the invoice number, referred to the invoice column, and used zero for the exact match.
  • Third, the match function returns the cell number of the invoice from the range.
lookup to left with index match returns row number

And in the end, INDEX uses that number to return the amount by positioning the cell from the amount column.

lookup to left with index match returns amount

3. Approximate Lookup

Just like VLOOKUP, you can also use INDEX/MATCH for approximate lookup.

Approximate lookup can be useful when the value for which you are looking is not there on the list and you want to get the closest match for that. In the below table, you have a list of grades according to the marks.

approximate lookup index match data table min

And, if you want to get a grade of 79, you can use the below formula.

=INDEX(B2:B6,MATCH(D3,A2:A6,1))

approximate lookup index match enter formula min

…here’s how this formula works

In this formula, we have used 1 in the match function for the match_type which allows it to perform an approximate lookup. It returns the first value which is lower than or equal to the lookup value.

approximate lookup index match returns grade

For 79, the first lowest value is 75, and for 75 grade is B. That’s why you get a B in the result.

4. Horizontal LOOKUP

As you know HLOOKUP is for a horizontal look up but you can also use INDEX and MATCH for this. Here in the below data table, you have a horizontal table for monthly sales and you want to get the sales value for “May”.

horizontal lookup index match data row

And the formula will be:

=INDEX(amount,0,MATCH(lookup_month,months,0))

horizontal lookup index match enter formula

…here’s how this formula works

In the above formula, instead of using MATCH in the row_num argument of the index, we have used it in column_num. And, match returns the column number of May month.

horizontal lookup index match returns column number

And then INDEX returns the value from the result column according to the position number.

horizontal lookup index match returns amount

5. Two-Way Lookup

In a two-way lookup, we need to get a value from a table. Just have look at the below table where you have zone-wise and product-wise sales amounts.

two way lookup index match sales data

Now, if you want to get the sales amount of a product for a particular zone, you need a two-way lookup and for that, you need to use a combination of INDEX MATCH MATCH. Yes, here you need to use MATCH twice.

In a normal combination of INDEX and MATCH, you use MATCH for the row number but in a two-way lookup, you need to use it for the column number as well. The formulas will be:

=INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

two way lookup index match enter formula

…here’s how this formula works

In the above formula, to get the sales to amount from the table you have used the index function and then the match function for its row_num and column_num argument. The match function which is the column_num argument returns 5 as the value Product-D in the 5th row in the range you have referred.

two way lookup index match returns row number

And, the match function which is in the row_num argument returns 2 as the value north zone is in the second column in the range you have referred.

two way lookup index match returns column number

Now with these values, the index function has returned the value which is in the 2nd column and 5th row: 1456.

two way lookup index match returns sales amount

6. Case Sensitive

If you face a problem when you have two same values in a list or in a column but in a different text case, you can perform a case-sensitive lookup to look up the right value. Let’s have a look at below student list where you have the first name and in the second column, you have scored.

case sensitive lookup index match student data

And, at first, there are names that are the same but in different text cases. For example, John Parker and JOHN Mathew. So let’s say, you want to look up the marks of “JOHN” not “John”, you can create an exact match lookup with INDEX and MATCH. And the formula will be:

=INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

case sensitive lookup index match enter formula

…here’s how this formula works

Here in this formula, you have used the EXACT function within the match function. As the match function is not able to lookup for a case-sensitive value and EXACT is a perfect function for this.

It can compare two values and return TRUE if they are exactly the same (including the case) but you need to enter this formula as an array because you have to compare the entire column with a single value in EXACT. When you enter it, it will return an array like this.

=INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

case sensitive lookup index match extact return array

From here, you need to use the match function so that you can get the position of TRUE from the array.

case sensitive lookup index match returns row number

At this point, you have the cell number (row number) of the value you need to look up. And, you can use INDEX to that value from the column.

case sensitive lookup index match returns student score

Important Point: If you get more than one TRUE with EXACT, the match will just return the number for the first TRUE

7. With Wildcards with INDEX MATCH

Wildcards are highly useful. You can perform a partial lookup using wildcard characters. And, the best part is, like all other lookup formulas, you can use wildcards with index and match as well.

Just have a look at the below list of names where you have both first and last names for employees and their ages.

lookup wildcards index match data

From this list, you need to get the age of a particular employee (Sondra). But the thing is you just know the first name.

And, if you use an asterisk then you can look up the age of Sondra using the first name. For this, the formula will be:

=INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

lookup wildcards index match enter data

…here’s how this formula works

An asterisk is a wildcard character that can replace n number of characters. So, when you have used it after the first name it has replaced the last name.

lookup wildcards index match returns row number

8. Lowest Value

Let’s say you have a list of students with their scores like below. And now, from this list, you want to look up the name of the student who has the lowest score.

lookup lowest value index match data

For this, you can use the MIN function with index and match and the formula will be:

=INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

lookup lowest value index match enter formula

You have students’ names in column A and their scores in column B.

So, when you insert this formula in a cell and hit enter, it will return the name of the student with the lowest score i.e. Librada Bastian.

Explanation

In this formula, we have three different parts.

In the first part, the MIN function returns the lowest score.

lookup lowest value index match returns value

After that, in the second part, the match function returns the cell for that lowest score.

lookup lowest value index match returns row number

In the end, the index function returns the value from the student name column using the same cell position returned by match.

lookup lowest value index match return name

Quick Tip: In the same way, you can also get the name of the student who gets the highest score.

9. Top nth score

Now think like this, you have a list of students with their scores in exams and from this list, you want to get the name of the student who got the 2nd highest score.

top nth score index match data

The thing is you don’t know what’s the second-highest score.

Normally, when you lookup for a value with lookup formulas, you are sure about the value for which you are looking. But, here you don’t know what’s the second-highest score.

So, for this, you can combine a large function with an index and match it. The large function will help you to figure out the 2nd highest value from the range.

And, the formula will be:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0)) 

top nth score index match enter formula

…here’s how this formula works

In this formula, you have used the large function inside the match function for the lookup_value argument. And in the large function, you have mentioned score range and 2 to get the second highest value.

top nth score index match large return second highest value

Once the large function returns the second largest value, the match function used that value and returns the cell number for that.

top nth score index match return row number

And in the end, the index function uses that cell number and returns the student’s name.

top nth score index match returns name

10. Multiple Criteria

Normally, the combination of index and match is meant to look up a single value. And that’s why you use a single range in the match function.

But, sometimes when you are hit with data in the real world you need to use multiple criteria to lookup up a value.

Let’s take the example below. Here you have a list of products with multiple details like product name, category, and size.

And from this data, you want to get the price for a particular product using all the criteria.

multiple criteria index match data

So the formula will be:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0)) 

multiple criteria index match enter formula

Note: This is an array formula, so you need to enter it using ctrl + shift + enter.

…here’s how this formula works

In this formula, you have three different arrays to match three different values and these arrays return TRUE and FALSE where values are matched.

After that, when you multiply them with each other you get an array or something like this.

=INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

And match function returns the position of 1 for the array.

multiple criteria index match return row number

In the end, INDEX returns the price from the price column using the number returned by the match.

multiple criteria index match return unit price

Quick Tip: If you don’t want to use an array formula then you can use the conditional SUMPRODUCT.

11. First Numeric Value from a Range

Let’s say, you have a list in which you have both text and numeric values, and now from this list, you want to get the first numeric value.

first numeric value index match data

For this, you can combine, the ISNUMBER function with index/match. ISNUMBER can help you to identify which value is a number and which one is a text.

The formula will be:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0)) 

first numeric value index match enter formula

You need to enter this formula as an array (using Ctrl + Shift + Enter).

…here’s how this formula works

In this formula, ISNUMBER returns an array equal to the length of the list and in this array, you have TRUE for the values which are numbers, and FALSE for the rest of the values.

first numeric value index match number array

After that, you used the TRUE in the match function as a lookup value. So, it returns the position number of the first TRUE from the array.

first numeric value index match returns row number

In the end, using that position number index returns the first numeric value.

48-first-numeric-value-index-match-return-first-number-min

12. Get the First Non-Blank Value

Let’s think like this, you have a list of values where some of the first cells are blank and you want to get the first non-blank value.

first non blank index match data

And, you can use this formula to get that first non-blank value.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0)) 

first non blank index match enter formula

…here’s how this formula works

We need to split this formula into three different parts to understand it wisely.

First of all, you have used the ISBLANK function inside the match function to get an array where you have TRUE for blank cells and FALSE for non-blank cells.

first non blank index match isblank array

Second, MATCH returns the position number of the first TRUE from the array returned by ISBLANK.

So at this point, you have the cell number of the first non-blank value.

first non blank index match returns row number

Third, the index function simply returns the first non-blank value from the list.

first non blank index match returns value from first non blank cell

13. Most Frequently Occurring Text

Now let’s say from a list of text values you need to count the most frequently occurring text.

In the list below, you have names.

But, there are a few names that are more than once.

most frequently occurring text index match data

So, now you need to get the name that has the highest occurrence in the list. You can use the below formula which is a combination of MODE, INDEX, and MATCH.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0))) 

most frequently occurring text index match enter formula

When you insert this formula, it will return “Tamesha” which is the most occurring name.

…here’s how this formula works

First of all, MATCH will compare the entire range of names with itself. And, while doing this, it will return an array where each text will represent its first position.

Let’s take an example of the name “Tamesha” which is our most occurring name on the list. Now, if you look at the list, it first occurred on the 8th cell and after that on the 12th cell.

But, if you look at the array, for all the positions where we have “Tamesha” it has returned 8 which is its first position.

most frequently occurring text index match array

After that, from the array returned by the MATCH, the mode function will return the most frequent number which is the cell number of the first occurrence of “Tamesha“.

most frequently occurring text index match mode returns cell number

And in the end, INDEX will return the text by using that cell number.

most frequently occurring text index match return name

14. Create a Hyperlink

Now, let’s say, along with looking for a value you also want to create a hyperlink for that value. In this way, you can quickly navigate to the cell where your lookup is in the column.

For example, in the below table, you need to get the age of a person. And, if you create a hyperlink for this value you can easily go to the cell where that value is.

create hyperlink index match data

And, for this, we need to use HYPERLINK + Cell with INDEX and MATCH and the formula will be:

Here’s how this formula works

Let’s split this formula into parts to understand it in a better way.

  • First of all, you have used index and match inside the cell function. And, when you use both functions within the cell function, you get a cell reference of the matched value instead of the matched value.
  • Second, you have concatenated “#” with the cell reference.
  • Third, you have again used index and match to get the matched value to use as a link text. In this way, you have the matched value as well as the link to the cell where that value is.

18 thoughts on “Reverse VLOOKUP in Excel with Index-Match (Right to Left)”

  1. MY DATA LIKE
    col1 col2 col3 col4
    anyname1 indicator1 indicator2 indicator3
    name2 MALE FEMLE TG Trance Gender
    xyz 245 200 5
    abc 520 450 10
    tom 600 500 8
    god 300 250 0
    bob blankdata
    output
    Indocor1
    male female
    xyz ??? ???
    i use this formula
    =VLOOKUP($A2,Sheet1!$A$1:$E$13,MATCH(B$1,Sheet1!$A$1:$E$1,0),0)
    =VLOOKUP(1stname,area,MATCH(2ndname,indicator,0),0)
    but male female data not in my excel help me

    Reply
  2. Dear Puneet, can you help me please – it would be appreciated. I have tried to resolve this myself, without success using your examples as a base in INDEX & MATCH, and VLOOKUP.
    I need a formula that will look at a cell containing a phrase (the phrase starts with a three letter code followed by other words), and then look specifically for the three letter code in that cell (using a list [range] of three letter codes for the search [14 three letter codes in the list]) and then provide me with the three letter code it has found from the list of codes.
    My spreadsheet contains a column filled with these phrases, so I wish to insert a column next to the phrases in which the code can then be shown.

    Reply
  3. Dear Puneet, This is the most comprehensive article on I-M that I have read so far and have learnt many new ways to use them. Thanks a lot.

    Reply
  4. how to use index and match for multiple results, like if I am searching one customer name “Ram” who have bought the goods multiple time, I want the formula to search Ram and show results again and again till it reaches the last cell of the table.

    Reply
  5. Good stuff. I would recommend a different approach for 10. Multiple Criteria. Instead of using an array formula add column E as a helper column with the formula =A2&”|”&B2&”|”&C2. The index/match formula then matches on column E and thus no need for an array formula. I used “|” as my delimiter in this example any delimiter that will not be found in the data is fine.

    Reply
  6. Hi Puneet,
    I am looking for a formula based on Index-Match to return last reord.
    I have a file with PO numbers in Column-A, Invoice Nos-B, Invoice Dates-C and invoice amount-D in adjacent columns.
    I need to search Last Invoice no., and Date for a particular PO number.

    Reply
  7. Please, there is an omission in section 14: Create Hyperlink where you said “And, for this, we need to use HYPERLINK + Cell with INDEX and MATCH and the formula will be:?
    So, what is the missing formula

    Reply
  8. Sir,
    Excellent work in excel vba…. I am one of the daily visitor to your website. I am proud to say, I have learned more and more in excel vba from ExcelChamps.

    Please explain the following both functions done in one column in excel.

    1.How to rounded off the numbers i.e (18.5 to 18.9) to next number i.e (19) using
    VBA.
    2.How to rounded off the numbers i.e (18.1 to 18.4) to the main number i.e (18) using
    VBA.

    Best Regards,
    YLNV Prasad Rao

    Reply
  9. Hi Puneet,

    I’ve been following your posts daily and am a great admirer of your work. Sadly, I couldn’t download the Sample File you tagged with your article yesterday named “How to use INDEX and MATCH in Excel – The Everything Guide”. Perhaps you already got the same feedback from your other readers. Could you please e_mail me the Sample File? Or, you can also fix the glitch if there’s any. Obviously, I’ll get back to your site again tomorrow and I can check then. Thanks much!

    Bikash

    Reply
    • Hey Bikash,

      I’m sad you had to face this problem. It was due to some conflict between the site and the tool I use here to serve files. .It’s sorted now you can download the files from the link I have shared above.

      Reply
  10. I really like your guides on EXcel, maybe I’m expecting more, but many times lately I’ve found the “Sample Files” (just above Conclusion) doesn’t work. I would think that this file would contain the images you use to generate this tutorial and thus have all these formulas and data?? Am I mistaken? I had the same basic problem with several lately. Also at the end of #14 on hyperlinks there is no formula after “and the formula will be:”

    Reply
  11. Hi Puneet…is there a formula to calculate the average of quartiles..I have used the quartile formula and obtained the values of the 4 quartiles.. now I need to know how to calculate the average of each quartile.. can you help?

    Reply

Leave a Comment