Lookup on the last reference cell
 

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!

Lookup on the last reference cell
 

Try this:

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

--

HTH,

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

"parrot" wrote in message
...
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!

Lookup on the last reference cell
 

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


On Fri, 20 Jun 2008 07:29:00 -0700, parrot
wrote:

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!

Lookup on the last reference cell
 

Exactly what I needed. Merci!

"RagDyeR" wrote:

Try this:

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

--

HTH,

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

"parrot" wrote in message
...
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!

Lookup on the last reference cell
 

Avec plaisir !

Appreciate the feed-back.
--

Regards,

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

"parrot" wrote in message
...
Exactly what I needed. Merci!

"RagDyeR" wrote:

Try this:

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

--

HTH,

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

"parrot" wrote in message
...
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!