Using Google Sheets is one of the most convenient ways of tracking different elements in the office or on a project. Using Vlookup formulas, you can quickly generate reports, compare data, and find patterns. However, if you’re new to all this, you might find it pretty challenging to Vlookup backwards in Google Sheets.
Unlike traditional forward lookup Vlookup applications, Vlookup backwards searches for values in the right-most column and retrieves data from columns to the left. Allowing you to perform Vlookup functions in reverse, which is essential when dealing with large data sets. But if you don’t know much about Vlookup backwards, don’t worry.
This post will look at how to Vlookup backwards in Google Sheets and provide examples. So keep reading to learn more.
Understanding Vlookup Backwards In Google Sheets and its Limitations
To better understand how Vlookup backwards works, it’s best to look at the traditional Vlookup function. Vlookup or Vertical Lookup is a function that retrieves a corresponding value to the leftmost column of a range in your Google Sheets. It’s most useful when retrieving information about a key value from a data set.
However, Vlookup only works to the right or forward lookups and cannot return information from columns to the left. This can be especially challenging if your key value is on the right and the information you need is to the left of the lookup column. In such instances, Vlookup backwards is necessary.
Vlookup backwards helps you to work backward from the right-most column to retrieve data from columns to the left. But before we get into examples, let’s look at the function syntax you will run into when using a Google Sheet.
Understanding The Vlookup Function Syntax
First, you need to understand how the Vlookup function works. If this is your first time running into Vlookup syntax, it looks like: =VLOOKUP(search_key, range, index, [is_sorted]).
As mentioned earlier, Vlookup stands for Vertical Lookup, which means it looks for values up or down a column, not horizontally. Here are the Vlookup parameters you need to know to make the function work.
- Search_key: the search_key, which also represents the values you’re trying to find in the lookup table, always leads in the parameter. You’re also using this value to search for its corresponding information.
- Range: represents the radius of your search on the main lookup table. This helps limit your searches to the most relevant values.
- Index: the index value determines which of the lookup table’s columns to use or return depending on your search term.
- Is_sorted: takes a TRUE or FALSE value represented by 1 or zero, respectively. It’s also the last parameter in the Vlookup syntax.
Vlookup Backward in Google Sheets – Practical examples
Now that you have a basic understanding of the syntax, it’s time to show you how to Vlookup backwards in Google Sheets. We will look at a simple Vlookup backwards example of a clothing store inventory.
The store’s data looks something like this.
Step 1. Pick a Cell
Select the cell where you want your results to be displayed. This will also be the cell you input your formula.
Step 2. Input Formula
Now that you have your cell highlighted, enter this formula to sort your data:
=Vlookup(E2,{$B$2:$B$5,$A$2:$A$5},2,FALSE)
Step 3. Press Enter
Hit enter, and the results should automatically populate your selected cell. Copy and paste the formula to fill the remaining spaces. The result should be a table that looks like the one below.
Now, all you have to do is repeat the same for the rest of the values to get your full results. But you don’t have to worry about going through the laborious process again. Google Sheets allows you to automate the entire process using the autofill option. Here is a short video showing you exactly how to do that.
.
Now let’s take things a gear higher and look at a more complex example.
How to Rearrange Google Sheets Columns Without Altering the Original Data
Considering that Vlookup backwards allows you to search for values to the left of the search_key, how can you use the same formula to rearrange information?
Take a look at our example below.
In the above table, the employee ID column would be our reverse Vlookup search_key, which means it’s on the far right. But if you want it to be the leading metric on your data set, you can still use the Vlookup backwards function to rearrange the data without altering the original data.
Step 1. Copy and Paste the Headers
The headers will be part of the column reference in the Vlookup function. You can rearrange the header section to fit your preference or keep it as it is.
Step 2. Pick a Return Cell
You should start by selecting the cell you want your results generated. This will also be the cell you input your formula; in our example, it will be the F5 cell below the employee header.
Step 3. Input the Formula
The formula we’ll use in this instance will look something like this:
=Vlookup($G$2,{$D:$D,A:A,$B:$B},MATCH(F4,{$D$1,A1,B1,$C$1},0)
Step 4. Enter
The result should match your reference ID number once you hit enter. If you did this right, you result should look something like this:
And that’s not all. If you drag the cell across the different headers horizontally, the match function should automatically fill the columns with the appropriate data. This video provides a better insight on how to do that.
Moreover, the relevant information should be displayed if you change the employee ID number value on top, as seen below.
This is how you can use the Vlookup function to rearrange your data sets. These are two examples of how reverse Vlookup can simplify your Google Sheets life and expedite your workflows.
Conclusion
Vlookups are helpful tools that allow you to quickly analyze data and organize information in a more relevant and consumable manner. However, traditional Vlookups are only limited to left-to-right analysis, which can present challenges when analyzing information on the left side of your Search_key.
That’s where reverse Vlookup or Vlookup backwards in Google Sheets comes in handy. It allows you to set your search_key on the far right side of your document and search from the data on its left. This gives you more freedom to manipulate your data and reach your desired results conveniently and much faster.
You also have the freedom to rearrange data in a more relevant format without altering it. If you’re trying to analyze, compare or reorganize data on your Google Sheets, you can now confidently use Vlookup backward and add the technique to your arsenal. So what applications will Vlookup backwards apply in your daily workflows? Let us know in the comments section below.
Muhammad Iqbal
Thursday 13th of July 2023
ΓΌ