Lookup

[Richard Mitchell]

Have two columns of non numeric data. The data in the first column can be duplicated several times, but the corresponding data in the second column will be unique to that entry. Looking for a formula that will look down the first column for the last entry of a given data set and return the data from the second column. -
Col1 Col2
1 ab
2 ac
3 ad
4 ae
1 ai
2 aj
3 ak
4 al
1 am
2 an
3 ao
4 ap

Given an input of "3" i am looking for a return of "ao"

Please help.

Thank you.

 

[Luke M]

Are you allowed to sort the data?

 

[bosco_yip]

Try,

=LOOKUP(2,1/(A1:A12=3),B1:B12)

Regards
Bosco

 

[Luke M]

Nice one, @bosco_yip ! I would have expected that to cause a DIV/0 error, but it appears LOOKUP is robust enough to handle it.

 

[Richard Mitchell]

Many thanks, works a treat.