How to vlookup values from right to left in Excel?

Vlookup is a useful function in Excel, we can use it to quickly return the corresponding data of the leftmost column in the table. However, if you want to look up a specific value in any other column and return the relative value to the left, the normal vlookup function will not work. Here, I can introduce you other formulas to solve this problem.

Vlookup values from right to left with VLOOKUP and IF function

Vlookup values from right to left with INDEX and MATCH function

Vlookup values from right to left with a useful feature

Vlookup values from right to left with VLOOKUP and IF function

To get the corresponding left value from the right specific data, the following vlookup function may help you.

Supposing you have a data range, now you know the age of the persons, and you want to get their relative name in the left Name column as following screenshot shown:

1. Please enter this formula into your needed cell:

2. Then, drag the fill handle to the cells you want to apply this formula to get all the corresponding names of the specific age. See screenshot:

Notes:

1. In the above formula, F2 is the value which you want to return its relative information, D2:D9 is the column that you are looking for and B2:B9 is the list that contains the value you wish to return.

2. If the specific value you lookup doesn't exist, a #N/A value will be displayed.

Vlookup values from right to left with INDEX and MATCH function

Except above formula, here is another formula mixed with INDEX and MATCH function also can do you a favor.

1. Enter or copy the below formula into a blank cell to output the result:

2. And then drag the fill handle down to your cells that you want to contain this formula.

Notes:

1. In this formula, F2 is the value which you want to return its relative information, B2:B9 is the list that contains the value you want to return and D2:D9 is the column that you are looking for.

2. If the specific value you lookup doesn't exist, a #N/A value will be displayed.

Vlookup values from right to left with a useful feature

If you have Kutools for Excel, with its LOOKUP from Right to Left feature, you can deal with this task quickly without remembering any formulas.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Super Lookup > LOOKUP from Right to Left, see screenshot:

2. In the LOOKUP from Right to Left dialog box, please do the following operations:

• Select the lookup value cells and output cells from the Lookup values and Output Range section;
• Then, specify the corresponding items from the Data range section.

Note: If you want to replace the #N/A error value with another text value, you just need to check Replace #N/A error value with a specified value option, and then type the text you need.

3. Then, click OK button, the matching records have been returned based on the lookup values from the right list, see screenshot:

Click to Download Kutools for Excel and free trial Now!

More relative articles: