VLOOKUP to the Left in Google Sheets

What Is VLOOKUP to the Left in Google Sheets?

VLOOKUP is a search function that is used to find data in one table by vertically searching a specified column with a search key and then returning the value from the same row. However, VLOOKUP can only search for values to the right of the lookup column, so there is no way to search to its left directly. Hence, we use a combination of functions to achieve VLOOKUP to the left in Google Sheets.

In the example below, we have a table depicting the details of some students, and we use the VLOOKUP in a specific way to retrieve the required values from the left of the search index. The syntax of the VLOOKUP function is as follows: =VLOOKUP(search_key, range, index, [is_sorted])

1. search_key is the item or value you want to find.
2. range is the range you want to include in the VLOOKUP function
3. index is the column index containing the value you’re looking for.
4. is_sorted refers to the final value and is true or false (true by default).

How to do VLOOKUP to the left in Google Sheets?

The VLOOKUP function searches for a value in the first column of a range and gives a result from a specified column to the right of the same row. However, for a left search, you can combine it with other functions, as shown below.

We usually employ a few tricks and tips to search to the left of the VLOOKUP function. One such way is to use VLOOKUP in a specific way.

Look at the data below. We have the IDs for some employees and their names and ages. We have to find the employee’s name with Age as a lookup value.

Step 1: The first step is to write the VLOOKUP function. Here, we enter the VLOOKUP in the following way.

=VLOOKUP(E4,{$B$2:$B$7,$A$2:$A$7},2,FALSE)

It is following the syntax of the VLOOKUP function in Google Sheets.

=VLOOKUP(search_key, range, index, [is_sorted]).

Step 2: Press Enter. You get the employee number to the left corresponding to the name.

Explanation:

• Here, we use the search term to the right of the LOOKUP column, which is “Sinha” at E4.
• {$B$2:$B$7,$A$2:$A$7} – The VLOOKUP is tricked into thinking that column B is the first and Column A is the second column.
• We are retrieving the value from Column A, which is considered the second column here. Hence, we give 2 as the index.
• The data is not sorted, hence, FALSE.

Examples

In Google Sheets, the VLOOKUP function searches for a value in the first column of a range and returns a result from a specified column to the right. However, to look up a value and return a result from a column to the left, you have to use a combination of the VLOOKUP with other functions. Let us look at some of the examples below.

Example #1

In this example, we have details of some flowers at a floral place, the amount sold by noon, and their discount percentages.

Now, we use the VLOOKUP to find which flower has been sold for what amount.

Step 1: We create a small table on the right in Column E for the amounts as seen below.

Step 2: Enter the VLOOKUP to the left in the Google Sheets function format to find the flower names. First, we enter the following formula in cell F1.

=VLOOKUP(E1,{$B$1:$B$5,$A$1:$A$5},2,FALSE)

Here, Column B is depicted as the first column and Column A as the second column in the array.

Press Enter.

Step 3: Now, drag the formula all the way to cell F5 to get the other values.

This example provides a flexible way of using VLOOKUP when the column you need to retrieve the value is to the left of the lookup column. A better way to perform this is by using a combination of INDEX and MATCH functions.

Example #2 – Using Index Match Function

As known by now, the VLOOKUP function cannot search to the left. However, to search from a right-side column and print a value on the left, we can look at the LEFT column in Google Sheets using INDEX and MATCH.

Let us look at the syntax of the INDEX MATCH formula.

INDEX (return_range, MATCH(search_key, lookup_range, 0))

Now, we have a table containing the ORDER ID, item name, and cost at a store. We will find the product name using the cost of the article.

Step 1: We use the INDEX Match formula as follows in cells E6 and E7.

=INDEX ($B$2:$B$7, MATCH(D6, $A$2:$C$7, 0))

Step 2: Press Enter. You get the name of the item on E6. Let us add the following formula in E7 and check the result.

=INDEX ($B$2:$B$7, MATCH(D7, $C$2:$C$7, 0))

As you can see, unlike VLOOKUP, when you use the Index Match formula, since the return column is directly referenced instead of an index number, there will be no problems even with structural changes like inserting a column in your lookup table which could cause VLOOKUP to throw errors or malfunction.   

Example #3 – Using XLOOKUP

Let us use the same table as above. Instead of using the VLOOKUP Google Sheets function in a complex way, let us use the XLOOKUP function to find the result to the left of the search value.

We can use the XLOOKUP function to look for values to the left and right of the lookup array. Let us use it to look up a value.

Step 1: Enter the following formula in cell H6.

The formula is =XLOOKUP(D6,$C$2:$C$7,$B$2:$B$7)

Which indicates XLOOKUP(search_key, lookup_range, result_range)

Step 2: Press Enter. You get the corresponding item.

Now, enter the following formula in H7.

=XLOOKUP(D7,$C$2:$C$7,$B$2:$B$7). Press Enter.

The XLOOKUP function is used to return the value in the result range based on where a match is found in the lookup. If no match is found, it returns the closest match.

Example #4

We use the Ampersand symbol to join many values for a search criterion. For example, let us look at the table below. 

Step 1: Consider that we want the employee ID of an employee starting with J. However, there are two results. So, you can apply wild card characters for search and concatenate it using &. Let us use the following formula to find the employee ID of the employee.

Enter the below formula in cell D5.

=VLOOKUP(“J”&”*ie”, {$B$2:$B$7, $A$2:$A$7}, 2, FALSE)

Step 2: Here, the formula retrieves the employee’s ID whose employee number begins with the letter J and also ends in “ie.” The wild card character * means any number of characters can be present between them.

Important Things to Note

1. Remember that VLOOKUP is not case-sensitive and does not distinguish between uppercase and lowercase letters.
2. When is_sorted is TRUE, the range’s specified first column should be in ascending order.
3. To handle scenarios when you may get an error due to the lookup value not being found, you can use the IFERROR function.
4. VLOOKUP can search partial matches using wildcard characters like the asterisk (*) and question mark (?).
5. You can use IF along with INDEX and MATCH for conditional logic or to handle multiple comparison scenarios.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the ISREF Function In Google Sheets, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is VLOOKUP to the Left in Google Sheets. We learn how to do a lookup with VLOOKUP & functions like INDEX MATCH in examples. You can learn more from the following articles.

Weighted Average in Google Sheets

Correlation Matrix in Google Sheets

NPER Function in Google Sheets