Excel 2007 : Reference last occurrence of text string

[gusviz]

The problem I have is an EXCEL programming problem. I am running EXCEL 2007 on XP. What is the EXCEL cell formula to put in a cell (for example“E5”) that will reference the “last” occurrence of a specific text string in column A (For example Chard ) but will show its corresponding column B content (for example its PH number 3.45)regardless of the number of data entries that will be inputted in the future for both column A and B.

A B C D E
1 WINE PH
2 Chard 3.24
3 Merlot 3.36
4 Cab 3.44
5 Merlot 3.38 xxx
6 Chard 3.26
7 Chard 3.45
8 Cab 3.41
9 Merlot 3.33
10


Thank you
Gus

[NBVC]

Try:

=Lookup(2,1/($A$1:$A$10="Chard")$B$1:$B$100)

adjust ranges to suit. You can replace "Chard" with a cell reference, if desired.

[Marcol]

Or try Sumproduct

In D2
=SUMPRODUCT(MAX((A2:A100=F2)*ROW(A2:A100)))

This can be elabolated on as in the attached.

[EDIT]
@ NVBC
I think you've missed a comma

=LOOKUP(2,1/($A$1:$A$10="Chard"),$B$1:$B$100)

I've added your solution to this attachment.
Hope you don't mind.

Hope this helps

[gusviz]

Try:

=Lookup(2,1/($A$1:$A$10="Chard")$B$1:$B$100)

adjust ranges to suit. You can replace "Chard" with a cell reference, if desired.

WOW thanks for the help. I have been struggling with this problem for weeks.

[gusviz]

Wow thanks for the help! Ive been working on this problem for weeks.

[gusviz]

Thanks for the help. I'm amazed at the quick response. The answer is great what a help. Thank you Thank you.

[NBVC]

[EDIT]
@ NVBC
I think you've missed a comma

=LOOKUP(2,1/($A$1:$A$10="Chard"),$B$1:$B$100)

Thanks Marcol, I obviously should have been sleeping instead, as I also screwed up the ranges... they are not the same size...

should've been more like:

=LOOKUP(2,1/($A$1:$A$10="Chard"),$B$1:$B$10)

or

LOOKUP(2,1/($A$1:$A$100="Chard"),$B$1:$B$100)

but not a mix...

[fred3]

How do I read the "1/" part of the expression?

[pjstevenson]

I have a similar question. The Lookup() formula works well if looking for an exact match "Chard", but I am looking for the last cell in column A that includes text, not an exact match ("e.g. "Swiss Chard", "chard", etc) and want to return the corresponding value from column B (which in my case happens to be a date). Lookup() apparently doesn't like wildcards because using "*Chard*" doesn't work. Help.

[Sum]

I have not understood below part:
1. Why is it '2' in lookup value instead of actual value?
2. Why '1/' is used for defining the lookup_vector?

Could you please help me to understand it.
Thank you.

[Sum]

I have not understood below part:
1. Why is it '2' in lookup value instead of actual value?
2. Why '1/' is used for defining the lookup_vector (or array)?

Could you please help me to understand it.
Thank you.

[FlameRetired]

I have not understood below part:
1. Why is it '2' in lookup value instead of actual value?
2. Why '1/' is used for defining the lookup_vector (or array)?

Could you please help me to understand it.
Thank you.

The 1/($A$1:$A$10="Chard") part generates 1's and divide by zero errors. If $A$1:$A$10="Chard" is TRUE the 1/ coerces a 1 and hence 1/1 which equals 1. If it's

FALSE coerces a 0 and hence 1/0 ........an error. Lookup seeks a 2 and not finding it takes the first next closest value to 2.

Did this help?

[Sum]

Thanks a lot for the above explanation. I thought I understood from it. Hence tried verifying.
However, when I replace the 2 with 1, it is giving unexpected results!!
Please find below the data and the formula:=LOOKUP(1,1/($E$72:$E$101=D66),$F$72:$F$101)

where D66 is A1

The above formula gives 6th instance as result even though there are up to 10 instances of A1.
If I delete all instances of A1 from 6 to 10 it gives third instance as result. (even though there are 4th and 5th in list)
If I delete third instance it gives fourth instance as result.(even though there is 5th in list)

So I am lost again!! Please help.

col E/Col F data
Column E: A1 A2 A3 A1 A2 A3 A1 A2 A3 A1 A2 A3 A1 A2 A3 A2 A3 A2 A3 A2 A3 A2 A3 A2 A3
Column F: A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD

Regards.