Results 1 to 5 of 5

Lookup Finding Values Not in List

Threaded View

  1. #1
    Registered User
    Join Date
    07-09-2007
    Posts
    2

    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!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1