Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or
0
0
0
s2smodern

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

 Look for a value from left to right: With this formula of Kutools for Excel, you can quickly vlookup the exact value from a list without any formulas. Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

#### Vlookup values from right to left with VLOOKUP and IF function

###### Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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:

Please enter this formula into your needed cell: =VLOOKUP(F2,IF({1,0},\$D\$2:\$D\$10,\$B\$2:\$B\$10),2,0) and press Enter key, you will get the correct result you need, see screenshot:

And then drag the fill handle to the cells you want to apply this formula to get all the corresponding names of the specific age.

Notes:

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

2. When you drag this formula down, the absolute references \$D\$2:\$D\$10 and \$B\$2:\$B\$10 stay the same, while the relative reference F2 changes to F3, F4, F5….

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

Type this formula: =INDEX(\$B\$2:\$B\$10,MATCH(F2,\$D\$2:\$D\$10,0)) and press Enter key to get the corresponding data you need, see screenshot:

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

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

Related articles:

How to use vlookup exact and approximate match in Excel?

How to lookup value to match case sensitive in Excel?

How to vlookup to get the row number in Excel?

### Recommended Productivity Tools

#### Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

#### Kutools for Excel

Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

200 New Features for Excel, Make Excel Much Easy and Powerful:

• Merge Cell/Rows/Columns without Losing Data.
• Combine and Consolidate Multiple Sheets and Workbooks.
• Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
• Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
People in conversation:
• To post as a guest, your comment is unpublished.
· 3 months ago
Thanks.....its works
• To post as a guest, your comment is unpublished.
· 4 months ago
Hi!

I'm trying to show a cell adjacent to a referenced cell when the referenced cell could be in one of two columns.

The referenced cell, M9, uses this function to find the upcoming date closest to today (i.e. which bill is due next):

=INDEX(\$K\$1:\$K\$160,MATCH(M9,\$L\$1:\$L\$160,0))

I want to cell M8 to show the AMOUNT due on that day, which is in the cell to the LEFT of the referenced cell in the list.

I figured out in O9 how to show it when M9 references a cell in a single column L:

=INDEX(\$K\$1:\$K\$160,MATCH(M9,\$L\$1:\$L\$160,0))

But I can't figure out how to have that apply when the referenced cell is in column N.

A few things I've tried in O10-O12 that didn't work:
=INDEX(\$K\$1:\$K\$160&\$M\$1:\$M\$160,MATCH(M9,\$L\$1:\$L\$160&\$N\$1:\$N\$160,0))
=INDEX(K1:K160,MATCH(M9,L1:L160,0))OR(M1:M160,MATCH(M9,N1:N160,0))
=INDEX(K1:M160,MATCH(M9,L1:N160,0))

Would love some help! Thanks!
• To post as a guest, your comment is unpublished.
· 4 months ago
• To post as a guest, your comment is unpublished.
· 4 months ago
Screenshot (not sure why it didn't attach above)
• To post as a guest, your comment is unpublished.
· 11 months ago
• To post as a guest, your comment is unpublished.
· 1 years ago
Thank you very much...
• To post as a guest, your comment is unpublished.
· 1 years ago
Thank u thank u so much