Vlookup to the Left

How To Do Vlookup To Left in Excel?

Vlookup to the left or reverse vlookup is done to find the respective values which are in the left column of the reference cell. It cannot be used to reference the cells to the left of the reference cell, but we can create formulas to find the values to use reference to the left of the excel, Index, and match are such formulas which are combined together or we can use conditional formulas in the lookup function to find values to the left.

To Use reverse lookup or Vlookup to the left, there are two functions that 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 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.

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

Method #1 – Vlookup Left in Excel 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 not possible 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 if Function.

  1. For example, consider the following data below,

    Vlookup to the left in excel Vlookup function is a very very useful function in excel. It is used to obtain values over a reference cell from a specific column. But Vlookup cannot be used simply on the left in excel. Introduction: The method of how to use vlookup to the left in excel is by using two different types of formulas: 1. Using Vlookup with If function 2. Using Vlookup with choose function The first function we will dicuss is Vlookup with If function: Usually in Vlookup function we search for values from left to right in a data table by the normal vlookup function it is not possible to search for a specific values 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 if Function. For example consider the following data below, We have data for some employees and their employee id’s with their department name. In cell G2 we want to find the name of the employee in-respect to the employee id provided to us. If the data for the employee name was on the right side of the ID column we would have used the simple vlookup function to find out the value. 1. In cell G2 Write the following formula, 2. We will explain later how this function but to see the result press enter and see the result. 3. Now drag the formula to cell G6 and see the result. In the above formula we have created an array of two tables, One of ID and one of names. We are taking relative reference of id and searching them against the names from our array and show the exact match from the second column. In the above formula we are tricking excel to believe that the second column is actually A column. The Second method to Find Vlookup Values to the left is Using Choose Function: Similar to what we did with the if function we can also use choose function to find values using a vlookup function to the left of the data table. For the demonstration purpose, I will take the same data from above and make some slight changes in the data. Have a look at the data below, This time around we will find the depart name with respect to the Id provided. 1. In cell G2 write the following formula, 2. Again I will explain the use of function in detail below but first press enter to watch the result. 3. Drag the formula to the cell G6 and see the final result. In the above example we used choose function nested in Vlookup to find the value on the right of the cell. Let me explain choose function in detail: 1. {1,2} This is the array created for the data table. 2. In the first column we selected the data from B column which is our relative reference. 3. In the second column we selected the data from A column which is the data we want. 4. In our formula the name column is the first column on the left and the Id column is the second column on the right. 5. We used vlookup function to find the data from right to left using the choose function. 6. This is also known as reverse lookup in excel. Explanation of Vlookup to the Left in Excel: In normal Vlookup we need the column numbers to find the value from a reference cell. And in Vlookups we can only find the data which are in the right columns to the reference cell. Vlookup to the left or reverse vlookup is done to find the respective values which are in the left column of the reference cell. Another function apart from Vlookup used in Index and Match function. How to use Vlookup to the left in Excel: To Use reverse look up or Vlookup to the left there are two functions which 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 2. Choose Function. 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. Things to Remember in Vlookup to The Left in Excel: There are few things which we need to remember in Vlookup to the left in excel: 1. We need to create an array of data set for the reference. 2. The array reference must be absolute. 3. Vlookup to the left is also known as Reverse Vlookup in Excel.

    We have data for some employees, and their employee id’s with their department name. In cell F2, we want to find the name of the employee with respect to the employee id provided to us. If the data for the employee name was on the right side of the ID column, we would have used the simple vlookup function to find out the value.

  2. In cell F2, Write the following formula,


    Vlookup to the left in excel example 1

  3. We will explain later how this function, but to see the result, press enter and see the result.


    Vlookup to the left in excel example 1-2

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


    Vlookup to the left in excel example 1-4

In the above formula, we have created an excel array of two tables, One of ID and one of the 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 and searching them against the names from our array and show the exact match from the second column.

In the above formula, we are tricking excel to believe that the second column is actually A column.

Method #2 – Vlookup Left in Excel Using Choose Function

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

Example 2

This time around, we will find the depart name with respect to the Id provided.

In cell F2, write the following formula,

Formula example 2-1

Again I will explain the use of the function in detail below, but first press enters to watch the result.

Example 2-4

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

Example 2-3

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

Recommended Articles

This has been a guide to Vlookup to the Left in Excel. Here we discuss how to use the vlookup function from Right to Left in Excel using 1) IF Function & 2) Choose Function along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *