To perform a reverse Vlookup in Google Sheets there are different formula options.

There are combination formulas like Index-Match and Indirect-Match to do a left side Vlookup. Trust me, Vlookup itself can do a reverse Vlookup in Google Sheets!

What’s Reverse Vlookup in Google Sheets?

In Vlookup the search key should be from the first column of your data/range. In reverse or left-hand side Vlookup, the search key can be from any column in the range.

The below example depicts the role of Vlookup and the concept of reverse Vlookup in Google Sheets.

Vlookup Formula Example:

In my data range, the student names are in the first column. So when I want to find the mark of a student, I can use his/her name as the search key in Vlookup.

Here is an entirely different scenario.

Suppose you don’t remember the name of the student. But you know his/her roll number.

Since the student roll numbers are in the second column, in a conventional way you are not permitted to use it in Vlookup as the search key.

Of course, you can get the mark using the range C2:D5 instead of B2:D5 in Vlookup. By changing the range reference you are making the search column as the first column.

Then can you give me one Reverse Vlookup Formula Example?

Vlookup Output from Left of the Search Column

Here is one simple example.

What about finding a student name using his roll number?

Here the search column is column B and the output column is column A, i.e. in the left side of the search key column.

I hope you could understand why the topic reverse/left side lookup is relevant.

Formula Options to Reverse Vlookup in Google Sheets

I am providing you different formula options to perform a reverse Vlookup in Google Sheets.

Besides you can find the pros and cons of each formula. Let me begin with the most popular Index-Match combination in Reverse Vlookup in Google Sheets.

Needless to say most of the Google Sheets, as well as Excel users, depend on this combination for a left side lookup. Index-Match is very popular.

Reverse Vlookup in Google Sheets Using Index-Match

I am using the below table for the Vlookup Left Side example. It’s slightly different from the earlier table.

Find the mark of the “Roll No.” 2. The result will be 95. The search key i.e. is Roll No. 2 is in cell F3.

Formula:

=index(B2:D5,match(F3,D2:D5,0),2)

Here is the Index syntax:

INDEX(reference, row offset, column offset)

In this, the reference is the data range B2:D5.

In order to get your desired value, you should provide the number of rows and columns to offset from the range.

We know the number of columns to offset, i.e. 2. Because we want the mark of the student to be extracted from the second column in the range.

We don’t have the row number to offset. What we have is the search key, i.e. roll number. Using this search key we can find the row to offset.

So in row offset, we can use the Match formula to return the offset number.

MATCH(search_key, range, [search_type])

Just provide the search key, which is the roll number in Match and select the range that contains the roll number. No doubt put the search type 0 as our data is not sorted.

The Match formula would return the offset row number.

Pros:

Easy to understand and use.

Cons:

Not flexible enough. It doesn’t support Array Formula for multiple search key and expanding results.

Additional Index-Match Resources

1. Multiple Conditions in Index Match in Google Sheets.
2. Case Sensitive Reverse Vlookup Using Index Match in Google Sheets.

Indirect-Match in Reverse/Left Side Lookup in Google Sheets

Formula:

=indirect("C"&match(F3,D1:D5,0))

I think it is the simplest way to lookup left or an alternative to the left side Vlookup in Google Sheets.

This formula combination is unknown to many. I haven’t seen anyone using this or any documentation of this combination so far.

Here the Match formula used is almost the same as the above in the Index-Match. But Here there is one difference.

The Match in Index-Match returns the row offset in the data range. It’s the relative position of the search key in the data range.

Here in Indirect-Match, the Match formula range is not D2:D5, here it’s D1:D5.

Since I have selected the range from the very first row, the Match formula returns the row number of the search key.

Now we have the row number of the search key. What we want is the mark of the student from column 3 (column C).

Again count the column from the very first column from your sheet. Not in your data range.

Just join this column letter “C” with the match formula to return the cell address of the to be extracted value.

"C"&match(F3,D1:D5,0)

Result: C4

The Indirect in the formula uses this cell address to extract the value from that cell.

Pros:

Easy to use, once learned.

Useful in conditional formatting.

Cons:

Not flexible enough. It doesn’t support Array Formula.

Additional Indirect-Match Resources

1. Transform Match Function to Vlookup or Hlookup in Google Sheets.
2. Highlight Intersecting Value in Google Sheets in a Two Way Lookup.

Reverse Vlookup in Google Sheets Using Vlookup Itself

Formula:

=vlookup(F3,{D2:D5,B2:C5},3,0)

For the left side aka reverse Vloolup, the best formula to use in Google Sheets is Vlookup itself! Think out of the box!

The problem with a normal Vlookup is that it only searches down the first column. We can’t change that.

But we can do one thing. Our search key in the above example is in the third column. We can move it as the first column, virtually!

Yes! Instead of using the data range as it is, which is B2:D5, create a virtual data range using Curly Brackets inside the Vlookup.

{D2:D5,B2:C5}

This is our data range for Vlookup. In this, the first column contains the roll numbers. So without violating the Vlookup rule/standard, we can do a reverse lookup in Google Sheets.

There is one important point to note. When you are creating a virtual range for Vlookup as above, your search key is obviously moved to the first column. Along with that, the value to extract column (mark column) may also get changed.

In our case, it’s now the third column.

Pros:

This formula retains all the virtue of normal Vlookup formula. You can use multiple search keys and multiple column outputs are also possible.

Cons:

A beginner may find it a little difficult to digest.

Not useful in conditional formatting.

Additional Vlookup Resources

1. How to Use Vlookup to Return An Array Result in Google Sheets.
2. Multiple Values Using Vlookup is Possible [How to].

Final Verdict (Left Side Vlookup)

If you ask me to choose a formula for reverse Vlookup in Google Sheets, I will definitely suggest the Vlookup. But if you want to conditional format a Lookup value, then go for Indirect-Match, not Index-Match or Vlookup.

That’s all. Enjoy!