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

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.

doc-vlookup-function-6

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble 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:

doc-vlookup-to-left-1

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:

doc-vlookup-to-left-1

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.

doc-vlookup-to-left-1

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


arrow blue right bubble 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:

doc-vlookup-to-left-1

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

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

Kutools for Excel

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

gold star1 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,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    asr · 3 months ago
    Thanks.....its works
  • To post as a guest, your comment is unpublished.
    Sarah Tanner · 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.
    Jajoo · 11 months ago
    very confusing, is there any youtube video about this?
  • To post as a guest, your comment is unpublished.
    Md. Nazmul Hoque · 1 years ago
    Thank you very much...
  • To post as a guest, your comment is unpublished.
    Manmohan · 1 years ago
    Thank u thank u so much