# Excel Lookup Not returning Proper Result

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:

``````switch(F5){
case "apple":
return "1";
break;
case "pear":
return "2";
break;
case "mango":
return "3";
break;
case "kiwi":
return "4";
break;
}
``````

Below is my Excel Lookup Function:

``````=LOOKUP(F5,{"apple","pear","mango","kiwi"},{"1","2","3","4"})
``````

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

-
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

`=LOOKUP(F5,{"apple","kiwi","mango","pear"},{"1","4","3","2"})`
....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]
`=HLOOKUP(F5,{"apple","pear","mango","kiwi";"1","2","3","4"},2,0)`