Find last non-blank text value

[CptnDiego]

[TABLE="width: 400"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Look For
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]A
[/TD]
[TD]A
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]C
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]E
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]F
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]G
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]H
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]I
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hiall,

I need Excel to look for a specific text in column 'A' and return thelast non-blank corresponding value in column 'B'. I know how to achievethis this when values in column 'B' are numeric via the lookup function. However,I have not found a way to do it when column 'B' is text.

In the table below above, when I search for 'A', I would likethe result to be 'F' (the last non-blank result). As you can see,I can achieve this when column B contains only numericvalues but not when they are text.

*I usethe following formula if column B contains only numeric values:{=LOOKUP(9,99999999999999E+307;IF($I$3:$I$13=$L$3;IF(($J$3:$J$13)<>0;$J$3:$J$13)))}

Any insight or idea on how to do this for text entries is appreciated.

Have a great day,

 

[steve the fish]

This should work:

=LOOKUP(2,1/(($A$1:$A$10=C1)*($B$1:$B$10<>"")),$B$1:$B$10)

 

[CptnDiego]

Thanks a lot. Works like a charm.