vlookup return first matching value which is not zero

[robknight]

Hello,

Any help with this one would be MUCH appreciated - thanks in advance!

I need to use a vlookup to match a code in column 1 and return the price from column 2.

There are multiple rows with the same code in column 1. The table is sorted, so all of the same codes are bunched together.

However, some of the prices in column 2 are zero.

I do not want a zero returned. I want the first non-zero price returned which matches the code I am looking for.

Example:

Code Price
ABC 0.00
ABC 0.00
ABC 3.40
ABC 5.24

When doing a vlookup for code ABC, I would want the price 3.40 returned.

Can anyone help please?

Thanks!

Rob.

[Phuocam]

Try this ...

=INDEX(B2:B10,MATCH(1,INDEX((A2:A10="ABC")*(B2:B10<>0),0),0))

[robknight]

Works perfectly, thanks!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.