Vlookup from Bottom to Top in Google Sheets

GiGiKing·March 13, 2024·0 comments

Are you tired of always searching down the first column of a table when using Vlookup in Google Sheets? Well, did you know that Vlookup from bottom to top is actually possible? In this article, I’ll show you how to achieve this and provide you with some handy tips along the way.

How to Vlookup from Bottom to Top in Google Sheets

By default, Vlookup searches down the first column of a selected range. But what if you want to search up instead? Well, the trick is to flip the table inside the Vlookup formula. This simple adjustment will allow you to meet your requirements without any hassle.

If you’re familiar with flipping a table, then using Vlookup from bottom to top will be a piece of cake. In case you need a refresher, I have already posted a tutorial on how to flip a column in Google Sheets. You can find it here.

Now, let’s take a look at the formula that will flip the table for us:

=SORT(B2:C,ROW(A2:A)*N(B2:B<>"") ,0)

You can use this formula as the range in Vlookup. But please note that this formula is based on the assumption that your sample data is in the range B2:C. If your data is in a different range, I’ll explain how to modify the formula later on.

To give you a visual representation, here’s an example of how the flipped table looks like:

flip vlookup range

Now that you know how to flip a table in Google Sheets, let me provide you with the formula to perform a Vlookup from bottom to top.

=VLOOKUP("Banana", SORT(B2:C,ROW(A2:A)*N(B2:B<>"") ,0), 2, FALSE)

In this example, I have used a flipped table as the range (underlined in red) in Vlookup to search up the column B2:B for the key “Banana”.

Bottom to Top Lookup using the Lookup Function in Google Sheets

If flipping the table seems like too much of a hassle, don’t worry! There is an alternative method using the Lookup function in Google Sheets. However, there is one important thing you need to know – the Lookup function is only meant to be used with sorted columns. But don’t worry, I’ll share a trick with you on how to use Lookup in an unsorted range.

Here’s the formula you can use instead of Vlookup to search up in a column:

=ArrayFormula(LOOKUP(2,1/(B2:B7=E1),C2:C7))

Please keep in mind that the Vlookup formula has certain benefits over the Lookup function in this case. With the Vlookup formula that searches from bottom to top, you can use multiple search keys and even perform a leftward Vlookup, among other things. If you’re interested in exploring more Vlookup formula variations, I highly recommend checking out my guide on Vlookup in Google Sheets – 10 Formula Variations, Tips and Tricks.

As an example, let me show you how to use Vlookup from bottom to top with multiple search keys:

Vlookup from Bottom to Top - Multiple keys

That’s all you need to know about performing a Vlookup from bottom to top in Google Sheets! I hope you found this article helpful. If you’re interested in diving deeper into the world of Google Sheets, be sure to check out Crawlan.com for more useful guides and resources.

Additional Resources: