Excel Help

[soapnumpty]

Hi
I'm trying to enter a LOOKUP formula which will look up one of three lists, depending on the answer entered in a certain cell.

For instance if cell B11 says X I want it to look up from list 1
If B11 says Y I want it to look up from list 2
If B11 says Z I want it to look up from list 3

I can only manage to get two options as follows
=IF(B11="X",HLOOKUP(C14,List1,2,1),HLOOKUP(C14,List2,2,1))
So if B11 = X go to list1, and if B11 doesn't = x go to list2.

Doea anyone know how to add a third option?

Many thanks.

[auntflo]

i think you need a nested if statement so
=IF(B11="X",HLOOKUP(C14,List1,2,1),IF(B11="Y",HLOOKUP(C14,List2,2,1)),HLOOKUP(C14,List3,2,1))
might have missed a bracket or two, but that's he idea

[vascop]

Just put another IF in as follows:
=IF(B11="X",HLOOKUP(C14,List1,2,1),IF(B11="Y",HLOOKUP(C14,List2,2,1),HLOOKUP(C14,List3,2,1)))
This assumes that cell B11 can only have the values X,Y and Z. If you want to catch the error if B11 is equal to something else (eg A) then add another IF just as I've done above, and make the ELSE part of the IF statement display an error message.

[soapnumpty]

You people are clever! I shall try those later. Thanks