Lookup on the last reference cell

[parrot]

Hello, I am trying to use vlookup on a column containing identcal cells. EX:

Col1 Col2
Sales 150
Costs 75
Sales 80
Costs 40
Sales 120
Costs 60

(Column 1 is not sorted an contains empty records)

The formula should retrieve the first occurance on "Sales" going from down
to the top: 120.
With the usual formula: =VLOOKUP("Sales",A1:B6,2,FALSE), I get 150. Thanks!

 

[RagDyeR]

Try this:

=LOOKUP(2,1/(A1:A6="Sales"),B1:B6)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hello, I am trying to use vlookup on a column containing identcal cells. EX:

Col1 Col2
Sales 150
Costs 75
Sales 80
Costs 40
Sales 120
Costs 60

(Column 1 is not sorted an contains empty records)

The formula should retrieve the first occurance on "Sales" going from down
to the top: 120.
With the usual formula: =VLOOKUP("Sales",A1:B6,2,FALSE), I get 150. Thanks!

 

[Gord Dibben]

Unless you have made a typo in your example..............

............first occurence of Sales in column A return 150 from column B


Gord Dibben MS Excel MVP

 

[parrot]

Exactly what I needed. Merci!

 

[RagDyeR]

Avec plaisir !

Appreciate the feed-back.