VLOOKUP - Multiple Entries, Find Last

[Decumanus Maximus]

I've been searching but I can't find the answer (to what seems a simple question). Here's an example:

-----ColumnA---ColumnB
RowA___1________10___
RowB___2________20___
RowC___3________30___
RowD___1________40___

If I'm using VLOOKUP on ColumnA for "1", it'll return "10" from ColumnB. I want it to return the last entry of "1" in ColumnA which would return "40" from ColumnB. That's it! I'm sure it is something simple but I'm not that smart.

Thanks for your help in advance.

 

[barry houdini]

Try

=LOOKUP(2,1/(A1:A4=1),B1:B4)

the red 1 is the lookup value......

 

[Decumanus Maximus]

Excellent! I appreciate the quick response plus time and effort. God bless.