lookup a value base on a range

[mrkhchan]

Hi Friend,

I have a table like that

product price
A123 10
A2456 5
B12 10
D5 5
C* 8 <- "C*" represent all product with the product code start with "C"

I can use vlookup to search the price of A123,A2456.
but if I want to search "C123","C456", how can I get the result is 8?

Thanks.

[Olly]

Try:

Please Login or Register  to view this content.

(adjust ranges to suit)

[mrkhchan]

Thanks for your help.
but it seem the formula is designed with a specific case.
if I also want to add the table with "XC*" with price "10", I only can add more
"iferror" in the formula.

[Olly]

Okay, so try this, entered as an ARRAY formula (Ctrl-Shift-Enter):

Please Login or Register  to view this content.