VLOOKUP to the Left

What Is Vlookup To Left In Excel?

VLOOKUP function retrieves values from the vertical dataset in a worksheet. However, users should know that the VLOOKUP function can work only from left to right and not from right to left. In order to address this limitation, you can use the IF and CHOOSE functions, to retrieve the values from the columns to the left of the lookup value column in the VLOOKUP function.

We must arrange the dataset in such a way that the lookup value column is located at the extreme left and the columns displaying the results are located to the right of this column to make VLOOKUP work. Any columns located to the left of the VLOOKUP function cannot be retrieved by the VLOOKUP to the left.

In this article, let us learn the limitations of the VLOOKUP to the left function and some of the alternative methods to overcome these limitations. For example, consider the below table showing the product IDs of fruits along with the respective sales. We will try to fetch sales and category information using VLOOKUP to the left.

The steps used to obtain results using VLOOKUP are as follows:

Step 1: Enter the VLOOKUP function in cell H2.

Step 2: Choose the lookup value as cell G2.

Step 3: Choose the table array from B2:D7 because our starting column should be the lookup column i.e., the Product ID column.

From the current selection of the table array, the category column is not even part of it because it is to the left of the lookup value column, i.e., Product ID. This is the primary limitation of the VLOOKUP to the left function.

In these situations, we must employ different methods to get the result from the desired column.

How To Do VLOOKUP To Left?

#1 – Using IF Function

The IF function is one of the logical functions commonly used. However, usage of the IF function within the VLOOKUP to the left function is uncommon.

Let us take the same example used earlier.

We must find the category of the product ID which is to the left of the lookup value column Product ID.

In such cases, we will use the following IF condition to shift the lookup value column (Product ID) to the left and the result column (Category) to the right.

In the above image, we have used the IF condition to swap the position of the columns that we have specified.

IF({1,0},B2:B7,A2:A7): This is used to obtain the TRUE or FALSE statement. Data we have entered in the TRUE argument will be used as the first column and data we entered in the FALSE argument will be used as a second column.

In the TRUE argument, we have given the Product ID column and in the FALSE argument, we have given the Category column. So, this will be converted to a new table with a lookup value column as the first and a category column as the second.

To check the functionality, change the 1 and 0 positions inside the curly brackets.

Now, inside the curly brackets, we have mentioned the FALSE argument i.e., 0 as the Category column. So, this will be treated as a first column and the TRUE argument, i.e., the Product ID column will be treated as the second column.

By using this logic, we can do the VLOOKUP to the left.

Step 1: Enter the VLOOKUP function in cell H2 and choose the lookup value as cell G2.

Step 2: Next we need to choose the table array, which is not as per the VLOOKUP criteria. So, use the IF Excel function as we have used to virtually swap the column positions.

It will make the ‘Product ID’ as the first column and ‘Category’ as the second column.

Hence, we end up getting the result column (Category) to the right of the lookup value column (Product ID).

Step 3: Mention the column index number as 2, because in the virtual swapping of columns, the Category column is the second column.

Step 4: Give the range lookup as FALSE to get the exact match.

Close the brackets.

Step 5: Press Enter key to get the result.

Even though the data structure is not as per the requirement of the VLOOKUP function, we have obtained the result.

Therefore, we have learned that we can obtain VLOOKUP to the left value using IF function.

#2 – Using CHOOSE Function

Now, we will learn to apply CHOOSE Excel function to retrieve values from the left of the lookup column for the same example.

The CHOOSE function chooses the values from the INDEX number.

The syntax of the CHOOSE function is

Let us learn how CHOOSE function works using the following example.

In the index number argument, we have mentioned number 3 and, in the values section, we have mentioned 5 values each separated by a comma.

Here, the CHOOSE function chooses the 3^rd value from the various values mentioned in the arguments i.e., ‘Hello’. It is the third value in the arguments.

Similarly, if we want to select multiple values, then we need to enter the index number in curly brackets.

In the above formula, we have mentioned the index number as 3 and 5, and the CHOOSE function chooses the 3^rd and 5^th positioned values respectively.

Similarly, we can use the same logic to convert the position of the lookup value column (Product ID) and the result column (Category).

We have selected 1 and 2 as the Index Number arguments, and in the first values section, we have chosen Product ID column (B2:B7) and then we have chosen the Category column.

Here, the CHOOSE function chooses the first column as the Product ID column and chooses the second column as the Category column. This new table structure is as per the requirement of the VLOOKUP function.

Enter this CHOOSE function in the table array argument of the VLOOKUP to the left function like we have applied the IF condition in the first example.

Clearly, we can see the results (from the left column of the lookup value column) using VLOOKUP and CHOOSE functions.

Alternatives To VLOOKUP To Left

#1 – Using INDEX MATCH function

Apart from the methods mentioned above, let us learn the alternative VLOOKUP to the left formula.

The combination of the INDEX and MATCH is used to retrieve values from left to right or right to left of the lookup value column.

The syntax of the INDEX Excel function is,

Array: In this argument, we need to mention the result column range.

Row Number: From the mentioned array row, we need the result. This will be dynamically provided by the MATCH Excel function.

The MATCH function helps find the row or column number of the lookup value.

The syntax of the MATCH function is,

Lookup Value: The value we are trying to find.

Lookup Array: The range of cells we are trying to find (the row or column number for the lookup value).

Match Type: The kind of matching we need for the lookup value. In this argument, we can provide the following inputs.

• 1: To find less than the value of the lookup value.
• 0: For an exact match of the lookup value.
• -1: To find greater than the value of the lookup value.

Let us use the same data used earlier in the first example.

Step 1: Enter the INDEX function in cell H2.

Step 2: Choose the array as the result column i.e., ‘Category’ column.

Step 3: Now we need to find the row number for the product ID ‘318’ in the range B2:B7.

To find it dynamically, we will use the MATCH function.

Enter the MATCH function for the row number argument of the INDEX function.

Step 4: Choose cell G2 as the lookup value because for this product ID, we are trying to find the row number.

Step 5: Choose the table array from B2:B7.

Step 6: Select the matching type as 0 for exact match.

Step 7: Close both the brackets and press Enter key to get the result.

The INDEX function will retrieve the value from the result column based on the row number provided by the MATCH function for the given lookup value.

#2 – Using XLOOKUP

XLOOKUP is the new function of Office 365 version with improved features compred to the VLOOKUP function. XLOOKUP is a lookup function which allows users to retrieve a desired value from a data set, both horizontally or vertically.

The syntax of XLOOKUP function in Excel is,

• Lookup_value: The value for which we are trying to retrieve the result from the return array (3^rd argument). It is a mandatory argument.
• Lookup_array: It is also a mandatory argument and can be either range or table array where we search for the lookup value.
• Return_array: It is also a mandatory argument and is the array from which we need to get the desired result for the lookup value provided in the first argument.
• [If_not_found]: If the lookup value is not available in the lookup array, we end up getting #N/A. However, we can get an alternative result instead of the default #N/A error by providing an alternative result here. It is an optional argument.
• [Match_Type]: In this argument, we need to specify the kind of match we need to do:
  • 0 – Exact Match, this will search for the exact match of the lookup value in the lookup array. If nothing is specified, 0 will be the default mode.
  • -1 – This will again look for an exact match but if not found, then, it will look for the next smaller item than the lookup value.
  • 1 – This too looks for an exact match but if not found, then, it will look for the next larger item than the lookup value.
  • 2 – This will be used to perform partial lookup operations using wildcard characters like an asterisk (*) and tilt (~).
• [Search_Mode]: In this argument, we need to specify how XLOOKUP searches for the lookup value.
  • 1 – It is the default searching option. It allows XLOOKUP to search from top to bottom in the lookup array.
  • -1 – It searches from bottom to top. It is helpful in case if we want to find the last matching result for the lookup value.
  • 2 – It helps in binary searching and the data needs to be sorted in ascending order. If not sorted, we will end up getting either the wrong result or error.
  • -2 – It helps us do binary searching and data needs to be sorted in descending order. If not sorted, we will end up getting either wrong result or error.

Let us use the same example here as well.

The steps used to obtain VLOOKUP values using XLOOKUP function are:

Step 1: Enter the XLOOKUP function in cell H2.

Step 2: Select the lookup value as cell G2.

Step 3: Choose the lookup array as the product ID column.

Step 4: The return array will be the category column. So, choose the cells from the range A2:A7.

While using XLOOKUP function, we don’t have to provide the match mode criteria. By default, it takes the exact match input.

Close the bracket and hit the Enter key to get the result.

The result obtained is exactly the same as the previous methods.

Therefore, we can use XLOOKUP function as an alternative to the HLOOKUP function as well.

Important Things To Note

• The VLOOKUP function requires the data to be organized where all the result columns should be to the right of the lookup value column. Any result columns to the left of the lookup value column should be calculated using different methods.
• In the IF function, 0 represents FALSE and 1 represents TRUE.
• The CHOOSE function chooses values only from the given values.
• The INDEX and MATCH functions return #N/A if the LOOKUP value is unavailable in the given range.
• XLOOKUP is available is Office 365 version only.

Frequently Asked Questions

Download Template

This article must help understand VLOOKUP to the left with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VLOOKUP to the Left. Here we discuss how to use VLOOKUP formula from Right to Left with examples and downloadable excel template. You may learn more about excel from the following articles –

• Alternatives to VLOOKUP
• ISBLANK in Excel
• OFFSET Function In Excel