Can VLookup return the LAST value in a range?

S

Steve

If I have a list:
A 10
B 3
C 5
A 8
D 6

How can I get VLookup to return to value associated with the last occurance
of A instead of the first (i.e the value 8)?
 
M

Max

One way, array-entered* in say, C1
=INDEX($B$1:$B$10,MATCH(MAX(IF($A$1:$A$10="A",ROW($A$1:$A$10))),IF($A$1:$A$10="A",ROW($A$1:$A$10)),0))

*press CTRL+SHIFT+ENTER to confirm the formula

Adapt the ranges to suit
 
A

Alan Beban

Max said:
One way, array-entered* in say, C1:
=INDEX($B$1:$B$10,MATCH(MAX(IF($A$1:$A$10="A",ROW($A$1:$A$10))),IF($A$1:$A$10="A",ROW($A$1:$A$10)),0))

*press CTRL+SHIFT+ENTER to confirm the formula

Adapt the ranges to suit
Click to expand...

Assumin that the list in A1:B5, if the functions in the
freelydownloadable file at http://home.pacbell.net/beban are available
to your workbook

=INDEX(VLookups("A",a1:b5,2),2)

Alan Beban
 
D

Dave Peterson

Another one:
=LOOKUP(2,1/(A1:A5="a"),B1:B5)


If I have a list:
A 10
B 3
C 5
A 8
D 6

How can I get VLookup to return to value associated with the last occurance
of A instead of the first (i.e the value 8)?
Click to expand...
 
Top