Lookup Finding Values Not in List

I'm a novice Excel user and I need some help with my lookup formula.

In worksheet A, I have foods and associated levels of protein. For example:

FOOD PROTEIN
apricot 1.4
chicken breast (boiled) 53.0
eggplant 0.0

In another worksheet, I want a user to be able to type in the name of a food item I've listed in. So, for example, if a user types "apricot" in cell A1, cell A2 will return "1.4".

My problem is that if the user accidentally types in an item that's not on the list, cell A2 will still return a result. For example, if the user types in "chicken" in cell A1, the returned value will be 1.4. If a user types in "donut", it will return 53.0.

I can see that the formula returns the value for the cell in the lookup vector that is alphabetically closest without going over. My current formula is as follows:

=LOOKUP(D5,'Worksheet A'!B2:B10,'Worksheet A'!C2:C10)

where D5 is where the user enters the food, B2:B10 in Worksheet A is where the corresponding food should be, and C2:C10 in worksheet A is the appropriate protein value to return.

What should I do to disallow the use of any item not in my list? I've attached a zip file containing what I'm trying to do. Any help is appreciated. Thanks!

In cell E5 in your example sheet you could enter the following :

=IF(ISNA(VLOOKUP(D5,'Master Food List'!B:F,2,FALSE)),"",B6* VLOOKUP(D5,'Master Food List'!B:F,2,FALSE))

Oh and if you need some help on vlookup type that into the excel help

Let me know how u go!

Hi Chronos,

Another idea would be to use data validation to only allow selection from a list that you define. Then, with steelmonkey's formula, no entry will give a blank - makes it a bit neater.

Ed

Steel Monkey
Wonderful! Thank you very much! A coworker had recommended I try using an IF statement to confirm that the input food matched one in my list, but I couldn't figure out how to do it. I've also never seen "ISNA" before. I'll be reading more about vlookups now. Thanks again.

EdMac
That's an awesome idea. I've never used Data Validation and didn't even know of it's existence. Thanks for bringing it to my attention and making that suggestion.

Thanks for the feedback - the more you learn, the more you realise there is more to learn!

Ed