Vlookup in reverse order

  • Thread starter Thread starter PJFry
  • Start date Start date
P

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
 
VLOOKUP works the same way. You can just MATCH & OFFSET (just like in
previous versions of Excel)
 
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))
 
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
 
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
 
Back
Top