Vlookup in reverse order

[PJFry]

I just upgraded to Excel 2007 and I was wondering if MS had added the
functionality to allow a lookup from right to left?

Looking through the help I did not see anything, but I thought it would be
work the question.

Thanks!
PJ

 

[Gary''s Student]

VLOOKUP works the same way. You can just MATCH & OFFSET (just like in
previous versions of Excel)

 

[Roger Govier]

Hi

The answer is No.

But you can always use Index and Match to achieve that functionality.
With data in A1:G10
Value to Lookup in column G held in cell J1
Column to Match with held in K1

=INDEX($A$1:$G$10,MATCH(J1,$G$1:$G$10,0),MATCH(K1,$A$1:$G$1,0))

 

[Peo Sjoblom]

What do you mean in reverse order?

If you mean that you lookup in for instance column 3 and returns the value
from column2 then Excel
has had that functionality ever since INDEX was introduced

=INDEX(B2:B100,MATCH("x",C2:C100,0))

will lookup x in C2:C100 and return the value from B2:B100



--


Regards,


Peo Sjoblom

 

[Bernard Liengme]

Nothing new in XL2007 but it can be done in all versions with MATCH and
INDEX
In A1:A5 enters numbers 20, 30, .. 60
In B1:B5 enter letters a, b, c, d e

In D1 enter the letter whose number is to be found 'backwards': I entered c
In E1 I used =MATCH(D1,B1:B5,0) to get the result 3 telling me that 'c' was
the third letter in the B column
In F1 I used =INDEX(A1:A5,E1) to get the value from the third cell in column
A
I can combine them as: =INDEX(A1:A5,MATCH(D1,B1:B5,0))

best wishes

 

[Chip Pearson]

No, you can still look only to the right in a VLOOKUP. See the "Left
Lookups" section at
http://www.cpearson.com/Excel/TablesAndLookups.aspx for formulas that
work like a VLOOKUP but allow you to look to the left.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)