VLOOKUP To The Left

Last Updated :

21 Aug, 2024

Blog Author :

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

find the VLOOKUP to the left in data

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.

result can be seen in the below image

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

  • 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 Excel
  2. Choose Function in Excel

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

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 excel 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.

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:

  • {1,2} This is the array created for the Data Table.
  • We selected the data from the B column in the first column, which is our relative reference.
  • In the second column, we chose the data from the A column, which is the data we want.
  • In our formula, the "Name" column is the first column on the left, and the "ID" column is the second column on the right.
  • Using the choose function, we used the VLOOKUP function to find the data from right to left.
  • It is also known as the reverse lookup in excel.

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.
VLOOKUP function only provides an NA error

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: -