IF= ----> NESTING LEVEL LIMIT?

Posted by Laura on November 14, 2001 10:26 PM

Hello,

Thanks God I found this site!!! Now I've learned how to use validation now i'm saving a lot of time!!!!
I have another silly problem, mates...

I have a long list of names. Each name has a fixed code, let's supposed a passport number...
simple task: how make excell to return the passport number when I tipe your name...??

I tried SUBSTITUTE... no way...
I tried IF here:
=IF(A1="","",IF(A1="Maria","410061",IF(A1="Juan","410056",IF(A1="Antonio","305010", .....))))
but the nesting level limit is 7, right?

Wich is the simplest way to do it... por favor!!!

Thanks in advance,

Ciao

Laura

Posted by Aladin Akyurek on November 14, 2001 11:15 PM

Laura --

Lets say that your list of names and codes (passports) in columns C and D. When you type in A1 a name, you want the passport number associated with this name (if any) to appear in B1.

In B1 enter: =IF(COUNTIF($C$1:$C$100, A1),VLOOKUP(A1,$C$1:$D$100,2,0),"Unknown")

This formula looks up the name in A1 in the table C1:D10 (the list of names and passports), if the name is listed in the table, returns the passport associated with that name. If the name in A1 is not listed in the table, the formula returns the word "Unknown".

Aladin

==========