This wikiHow teaches you how to do a reverse VLOOKUP (aka search from the bottom of the column to the top of the column) in Google sheets using the INDEX function.
Steps
-
Prepare your data. In this example, we will lookup a value from column A, and output the corresponding value in column B. So our lookup "table" is A:B.
-
Figure out what value you want to lookup. We will lookup a value in cell C1 for this example.Advertisement
-
Use the filter function. The filter function helps you find a set of values inside of a column. In this case, the filter will return to us the set of rows that contain our value in C1. The filter part of the formula is: filter(ROW(A:A), A:A=C1).
-
Get the highest row from the filter function by using the MAX function. The MAX part of the final formula is MAX(filter(ROW(A:A), A:A=C1)).
-
Use the INDEX function to get the value out of the row you discovered using MAX and FILTER.
- The final formula is =INDEX(A:B, MAX(filter(ROW(A:A), A:A=C1)),2).
- The index function is looking up the row we found using filter inside of the specified table, and returning the corresponding value in column B, the second column, which is why the 2 is used.
Advertisement
Expert Q&A
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Video
About this article
Thanks to all authors for creating a page that has been read 13,775 times.
Advertisement
