How to search up the first column of a table for a key instead of search down using Vlookup? Vlookup from bottom to top is possible in Google Sheets. You just need to flip the table inside the Vlookup formula for this.
I know you can use Lookup instead of Vlookup to search up a column for a key and return a value from the row found. But that’s not going to satisfy a Vlookup user because of the flexibility Vlookup offers.
At the end of this Vlookup tutorial, I’ll provide you how to Lookup the last occurrence of a key (which is equal to search up) in a column using Lookup. But before that here is the Vlookup formula.
How to Vlookup from Bottom to Top in Google Docs Sheets
Actually, by default, Vlookup will search down the first column of a selected range. So what I am going to do is, flip the table to use in Vlookup. By doing so we can satisfy our requirements.
If you know how to flip a table, then using Vlookup from bottom to top is not a tough task. All this you can see in the example section down.
I have already posted how to flip a column in Google Sheets. Here I am following the same approach.
If you want to learn it in detail see that tutorial here – How to Flip a Column in Google Sheets – Finite and Infinite Columns.
In the following example, I have my sample data in the range B2:C. See the formula that flips this table.
=SORT(B2:C,ROW(A2:A)*N(B2:B<>""),0)
This is the formula that we can use in Vlookup as the range.
In your Spreadsheet, you may have your data in another range. I’ll explai how to modify this formula for a different dataset range.
Suppose your dataset is in A1
=SORT(A1:E,ROW(A1:A)*N(A1:A<>""),0)
Hope you could understand how to flip a table in Google Docs Sheets. For the Vlookup example purpose, I am taking the data in the range B2:C (see the screenshot above)
Here is the formula to Vlookup from bottom to top in Google Sheets.
I have used a flipped table as the range (underlined in red) in Vlookup to search up B2:B for the key “Banana”.
Bottom to Top Lookup using the Lookup Function in Google Sheets
As promised here is the Lookup version. Here there is no need to flip the table. But one thing you must know.
Google Sheets Lookup function is ‘only’ for sorted column. Since our range is not sorted we have to find a way to use Lookup in an unsorted range.
In Excel and as well as in Google Docs Sheets, there is a similar trick to use Lookup in an unsorted range. Here is that – How to Use LOOKUP Function in an Unsorted Array in Google Sheets.
Now see the below formula. This we can use instead of Vlookup to search up in a column.
=ArrayFormula(LOOKUP(2,1/(B2:B7=E1),C2:C7))
The Vlookup has certain benefits over the Lookup in this case. With my Vlookup formula that search from bottom to top, you can use multiple search keys, leftward Vlookup etc.
This is similar to the normal Vlookup formula usage that I have detailed in my Vlookup formula variation guide – Vlookup in Google Sheets – 10 Formula Variations, Tips and Tricks.
As an example, I’ll show you how to use Vlookup bottom to top with more than one search key.
Vlookup Multiple Search Keys from Bottom to Top in Sheets
Here you just need to include the additional keys (E1:E2) in the Vlookup and wrap the entire Vlookup formula with ArrayFormula.
That’s all about Vlookup from bottom to top in Google Docs Sheets. Enjoy!
Additional Resources: