VLOOKUP To The Left

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is VLOOKUP To Left In Excel?

VLOOKUP to the left or reverse VLOOKUP is done to find the respective values in the reference cell’s left column. Therefore, we cannot use it to reference the cells to the left of the reference cell. Still, we can create formulas to find the values to reference Excel’s left. The INDEX and MATCH are such formulas that are combined. We can use conditional formulas in the LOOKUP function to find values to the left.

For example, we can find the VLOOKUP to the left in data as shown in the below image.

We can find the result by inserting the formula, =INDEX($A$2:$A$7,MATCH(G2,$B$2:$B$7,0)).

The result can be seen in the below image.

Let us learn VLOOKUP to the left in Excel in this article.

Key Takeaways

  • VLOOKUP to the left or reverse VLOOKUP can be used to find the respective values in the reference cell’s left column. Hence, it cannot be used to reference the cell to the left of the reference cell.
  • INDEX and MATCH formulas can be used to locate values to the left. Even conditional formulas in the LOOKUP function can be used to search values located to the left.
  • IF function and CHOOSE function in Excel in the VLOOKUP function can be nested for using the Reverse LOOKUP or VLOOKUP. They locate the values for a reference cell on the data table’s left column.

VLOOKUP Function Video Explanation

 

How To Do VLOOKUP To Left?

For using the reverse lookup or VLOOKUP to the left, two functions can be nested in the VLOOKUP function to find the values for a reference cell on the left column of the data table. They are:

For using the reverse lookup or VLOOKUP to the left, two functions can be nested in the VLOOKUP function to find the values for a reference cell on the left column of the data table. They are:

  1. If Function in ExcelIf Function In ExcelIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. read more
  2. Choose Function in ExcelChoose Function In ExcelChoose Function returns a value from the list of values in a given range. This function takes two mandatory arguments: the index number and the first value. The other values are optional to mention.read more

In the above functions, we create an array data table and make Excel assume that the data on the left column is actually on the right column and find the exact match.

#1 – Using IF Function

You can download this Vlookup Left Excel Template here – Vlookup Left Excel Template

Usually, in the VLOOKUP function, we search for values from left to right in a data table by the normal VLOOKUP function. It is impossible to search for a specific value from right to left. But in Excel, there are some tricks we can use to do that search. One such method is to use VLOOKUP with the IF function.

One such method is to use VLOOKUP with the IF function.

  1. Consider the following data below.


    Vlookup left Example 1

    We have data for some employees and their employee IDs with their department names. In cell F2, we want to find the employee’s name concerning the employee ID. If the data for the employee name were on the right side of the “ID” column, we would have used the simple VLOOKUP function to find the value.

  2. In cell F2, write the following formula.


    Vlookup left Example 1-2

  3. We will explain later about this function, but to see the result, press the “Enter” key and see the result.


    Vlookup left Example 1-1

  4. Now, drag the formula to cell F6 and see the result below.


    Vlookup to the left in excel example 1-4

In the above formula, we have created an Excel array of two tables: ID and names. We are taking relative reference in excelRelative Reference In ExcelIn Excel, relative references are a type of cell reference that changes when the same formula is copied to different cells or worksheets. Let's say we have =B1+C1 in cell A1, and we copy this formula to cell B2 and it becomes C2+D2.read more of ID, searching them against the names from our array, and showing the exact match from the second column.

In the above formula, we are tricking Excel into believing that an A column is a second.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Method #2 – Using CHOOSE Function

Similar to what we did with the IF function, we can choose the function to find values using a LOOKUP function to the left of the data table. For the demonstration purpose, we will take the same data above and make some slight changes to the data. Have a look at the data below.

Example 2

This time, we will find the department name concerning the ID provided.

In cell F2, write the following formula.

Vlookup left Example 2-1

Again, we will explain the use of the function in detail below. But first, press the “Enter” key to watch the result.

Example 2-4

Drag the formula to cell F6 and see the final result.

Example 2-3

In the above example, we used the CHOOSE function nested in VLOOKUP to find the value on the right of the cell. Let me explain the choose function in detail:

Alternatives To VLOOKUP To Left

#1 – Using Index Match Function

Consider the below table showing data of a musical shop with sample albums along with its price, genre and serial number.

Alternative - Method 1

Now, let us learn how to use INDEX function in Excel.

Step 1: First, we have to enter the INDEX function. Next, choose the Genre as array.

Step 2: Now, assume that we have to find the serial number, 318. Now, we should insert MATCH function.

Step 3: Now, assume that we have to find the serial number, 318. Now, we should insert MATCH function.

Next, enter G2, serial number as lookup value for MATCH function.

Step 4: Finally, we have to insert the table range, B2:B7 and enter 0 as exact match.

Vlookup to the left - Alternative - Method 1 - Step 4

Step 5: Press Enter key. We can find the result as shown in the below image.

Vlookup to the left - Alternative - Method 1 - Step 5

Likewise, we can use INDEX function to find VLOOKUP to the left.

#2 – Using XLOOKUP

Let us use the same example. Now, let us use XLOOKUP function to find the result corresponding to serial number, 116.

Vlookup to the left - Alternative - Method 2

The steps are:

Step 1: Enter XLOOKUP function in cell H2.

Step 2: Enter the complete formula.

The formula is =XLOOKUP(G2,$B$2:$B$7,$A$2:$A$7)

We don’t have to insert MATCH function while using XLOOKUP function.

Vlookup to the left - Alternative - Method 2 - Step 3

Step 3: Press Enter key.

Vlookup to the left - Alternative - Method 2 - Step 3 - Output

We can find the result as shown in the above image.

Likewise, we can use XLOOKUP function to find VLOOKUP to the left.

Frequently Asked Questions

1. Why INDEX and MATCH functions better than VLOOKUP to the Left?

INDEX and MATCH functions are better than VLOOKUP to the Left because VLOOKUP looks into the values from left to right. In comparison, INDEX and MATCH functions look into the qualities from left to right and right to left as well. Moreover, VLOOKUP conducts queries through vertical lines, while INDEX and MATCH functions query values through both lines and segments. 

2. What are the limitations of using VLOOKUP to the Left?

The major limitation is that there is a need for unique values for appropriate results. If there is a duplicate, then the LOOKUP function may give back the first matching value in the form of a result instead of the required output.

3. Why is XLOOKUP better than VLOOKUP to the Left?

XLOOKUP locates the values to the LOOKUP array’s left and right. In contrast, the VLOOKUP function only searches for the values to the right of the LOOKUP value column. Additionally, the XLOOKUP function enables one to customize the text in case of a valid match, but the VLOOKUP function only provides an #N/A error symbol.

This article is a guide to VLOOKUP to the Left in Excel. Here, we discuss using the VLOOKUP function from right to left in Excel using 1) IF function and 2) CHOOSE function, along with practical examples and a 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. Required fields are marked *