• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP where the look up column is not before the result column

Taryn

New Member
I need a formula that works in a similar way to a VLOOKUP but unfortunately in the data that it needs to link to, the look up value column appears after the result column, is there another formula than can do this?
I am not the owner of the data file so I cannot move the columns around
 
If you have XLOOKUP you can use that. If not, use INDEX and MATCH:
=INDEX(result_column, MATCH(lookup_value,lookup_column,0))
is the basic syntax.
 
To use VLOOKUP to perform a lookup to the left, you can use the CHOOSE function to reorder the lookup table.

For example:

=VLOOKUP(E5,CHOOSE({1,2},score,rating),2,0)

84349
 
If you have 365 now might be the time to start refactoring every formula to exploit the new functionality and get away from past practices.
Mind you, @bosco_yip's core formula
= CHOOSE({1,2}, score, rating)
offers a perfectly good way of reordering the column arrays even using 365. There are others though
= CHOOSECOLS(grades, 2, 1)
= HSTACK(score, rating)
 
Yes it seems like things have come along in terms of functionality, is there some sort of course you know of that addresses new formulas available in 365? I've just started using it when I changed jobs so have no idea of it's capabilities
 
If you have XLOOKUP function, the easiest way to return a column on the left of the column of research is :

1. Select the value to research

2. Select the column of research (only one column to select)

3. Select the column to return whatever if it's on the right or the left of the research's column

And I reuse the above example, that become >>

=XLOOKUP(E5,Score,Rating)

84352
 
Last edited:
Back
Top