Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a simple lookup function, which I am using in place of a Switch Case statement.

Following is what I am trying to accomplish:

    case "apple":
        return "1";
    case "pear":
        return "2";
    case "mango":
        return "3";
    case "kiwi":
        return "4";

Below is my Excel Lookup Function:


The problem: When F5 is equal to apple and pear I get 1, and 2 respectively and that is fine.

However, when F5 is equal to mango or kiwi I get 1, and not 3 or 4 respectively, which is what I expect.

Why is this happening? Am I using the wrong syntax? Is the above lookup function not adequate to use in place of a switch case statement? Is there a better way of doing this?

p.s. this link talk about using the lookup function in place of a switch case statement

share|improve this question
I don't have Excel in front of me, but I'm guessing it has to do with alphabetical order of your lookup values. Try sorting and see if it fixes your issue. I also think there's a switch in the lookup function for exact value that may work. – dav Mar 2 '13 at 18:01
up vote 1 down vote accepted

LOOKUP function requires the lookup range (your fruits) to be in ascending order

Try changing the order like this


....although if F5 is lemon that will give you 4 rather than #N/A so for exact match only you could use HLOOKUP like this [doesn't require sorting]


share|improve this answer
Awesome! I'm using HLOOKUP now! – xited Mar 2 '13 at 18:03

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.