VLOOKUP Question

[mjmeyer]

Good Afternoon,
I have a Vlookup question that may be very simple.

I have a named range "PriceList" as below:
A B C
Product Code Description Price
101 Chicken Lips $1.19
102 Choice Chicken Lips $1.99
107 Chicken Feet $1.12
108 Select Chicken Feet $1.60
109 Choice Chicken Feet $1.99
115 Chicken Knuckles $4.19
120 Chicken Wing Tips $0.19
121 Chicken Eyelids, Upper $5.19
123 Chicken Eyelids, Lower $17.19

I want a formula to return my message :"Product Code does not exist" if I
type in say, "103" as my Lookup Value instead of "#NA".
I want it to reflect the EXACT match of product code or my own error
message.

Thanks,
Mike Meyer

 

[Peo Sjoblom]

=IF(ISNA(VLOOKUP),"Product Code does not exist",VLOOKUP)


and the vlookup might look like

=VLOOKUP(H1,PriceList,2,0)

where H1 contains the product code, adjust the 2 to 3 if you want the price

 

[T. Valko]

Try this:

A1 = lookup_value

=IF(COUNTIF(INDEX(PriceList,,1),A1),VLOOKUP(A1,PriceList,3,0),"Product Code
does not exist")

 

[mjmeyer]

Thank you VERY much!
Works like a charm!!!!

Mike