Vlookup from Bottom to Top in Google Docs Sheets

Published on

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.

flip vlookup 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:E. Here is the modified formula for this range to flip the data.

=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.

Vlookup from Bottom to Top in Google Docs 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.

Vlookup from Bottom to Top - Multiple keys

That’s all about Vlookup from bottom to top in Google Docs Sheets. Enjoy!

Additional Resources:

  1. Vlookup Result Plus Next ‘n’ Rows in Google Sheets.
  2. Vlookup Skips Hidden Rows in Google Sheets – Formula Example.
  3. Comparison of Vlookup Formula in Excel and Google Sheets.
  4. How to Vlookup a Date Range in Google Sheets [Sorted/Unsorted Data].
  5. How to Highlight Vlookup Result Value in Google Sheets.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.