VLOOKUP to the Left

What Is Vlookup To Left In Excel?

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

It is one of the limitations of the VLOOKUP function.

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

In this article, let us learn the limitations of the VLOOKUP to the left function and some of the alternative methods to overcome the limitations of the VLOOKUP function in Excel.

First, let us understand the limitation of the VLOOKUP function when the data is organized in a way which is not suitable to apply VLOOKUP function.

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.

Vlookup to the Left Intro

The steps used to obtain results using VLOOKUP to the left 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.

Vlookup to the Left Intro Example

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 need to employ different methods to get the result from the desired column.

Key Takeaways
  • With the help of the IF and CHOOSE functions, we can retrieve the values from the left column of the lookup value column in the VLOOKUP function.
  • The IF function will return the table when we supply the TRUE (1) and FALSE (0) values in the logical argument in curly brackets.
  • Choose function also returns the table based on the position of the index number.
  • The INDEX and MATCH functions can be used as an alternative to VLOOKUP function.
  • The XLOOKUP function can retrieve the value from right to left and left to right as well.

How To Do VLOOKUP To Left?

#1 – Using IF Function

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

Let us take the same example used earlier.

Example 1

We need to find the category of the product ID which is on 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.

Vlookup to the Left Example 1.0

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.

Example 1.1

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.

Example 1.2

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.

Example 1.3

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.

Example 1.4

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

Close the brackets.

Example 1.5

Step 5: Press Enter key to get the result.

Example 1 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

Choose Syntax

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

choose 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 3rd 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.

choose Example 1

In the above formula, we have mentioned the index number as 3 and 5, and the CHOOSE function chooses the 3rd and 5th 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).

Vlookup to the Left Example 2.0

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.

Vlookup to the Left Example 2.1

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,

Index Function Syntax

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,

Match Function Syntax

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.

Vlookup to the Left Example 3

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

Example 3.2

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.

Example 3.3

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

Example 3.4

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

Example 3.5

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

Example 3.6

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

Vlookup to the Left Example 3 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 than the VLOOKUP function. XLOOKUP is a lookup function which allows users to retrieve the desired value from the data set, both horizontally or vertically.

The syntax of XLOOKUP function in excel is,

XLOOKUP Syntax
  • Lookup_value: The value for which we are trying to retrieve the result from the return array (3rd 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.

Example 1

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

Step 1: Enter the XLOOKUP function in cell H2.

Example 4.1

Step 2: Select the lookup value as cell G2.

Example 4.2

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.

Vlookup to the Left Example 4.3

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.

Example 4.4

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

Can you VLOOKUP to the Left?

With the traditional approach, VLOOKUP cannot retrieve the values from the left column of the lookup value column. However, by using the IF or CHOOSE functions, we can virtually alter the positions of the lookup value column in the table array argument of the VLOOKUP to the left function.

For example, look at the following data in Excel.



In cell F2 we need to find the ‘Product’ name for the Product Id ‘1044’. In the main table, lookup value column (Product ID) is to the right of the result column (Product).

Hence, we cannot use only the VLOOKUP to the left function.

So, apply the following combination VLOOKUP to the left formula and IF.

=VLOOKUP(E2,IF({1,0},C2:C7,A2:A7),2,0)

FAQ Result

Likewise, we can obtain results from the data with ease.

Does VLOOKUP have to go left to right?

Yes, the VLOOKUP to the left function has to go from left to right to retrieve values.

Why cannot VLOOKUP look to its left?

The VLOOKUP to the left function is designed in such a way that, when we select the table array argument, the first column should be the lookup column. Hence, any columns located before the lookup value column is not part of the table array. So, we cannot really specify the column index number for the left columns.

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published.