What's wrong with this if statement?

[Karen]

I have a formula that should work like this:

IF data in C11 = "Cingular" then lookup data in A11 & give me the cingular
number
IF data = "Nextel" then lookup A11 & give me the nextel number
IF data = "Aircard" then lookup A11 & give me the aircard number
Otherwise, leave the field blank

Here's the formula:
=IF(C11="Cingular",VLOOKUP(A11,Sheet1!$A$2:$G$23,7,FALSE),
IF(C11="Nextel",VLOOKUP(A11,Sheet1!$A$2:$G$32,6,FALSE),
IF(C11="Aircard",VLOOKUP(A11,Sheet1!$A$2:$G$32,8,FALSE)," ")))

It works for the first two criteria, but I never get the aircard number;
instead I get a #REF! error whenever Aircard is in C11. It even gives me
blank when there's no criteria, which is good.

How can I modify the formula to get all the if statements to work? Any help
is appreciated.

 

[Elkar]

The problem is with the 8 in your last VLOOKUP statement. Your range, A2:G32
only contains 7 columns, therefore, the 8 will return an error since it is
instructed to return the value from the 8th column in a 7 column range.

HTH
Elkar

 

[John C]

Your table only goes from columns A through G on Sheet1, which is 7 columns,
your Aircard is looking in column 8.

Could clean up the formula a bit as well...
=IF(C11="Cingular",VLOOKUP(A11,Sheet1!$A$2:$G$23,7,FALSE),
IF(C11="Nextel",VLOOKUP(A11,Sheet1!$A$2:$G$32,6,FALSE),
IF(C11="Aircard",VLOOKUP(A11,Sheet1!$A$2:$G$32,8,FALSE)," ")))

=IF(OR(C11="Cingular",C11="Nextel",C11="Aircard"),VLOOKUP(A11,Sheet1!$A$2:$H$32,6+1*IF(LEFT(C11)="C")+2*IF(LEFT(C11)="A"),FALSE),"")

 

[Mike H]

Hi,

You only have 7 columns in your lookup array and in the last lookup try to
return a value from Column 8. Change the G's to H's and it should be OK.

Mike

 

[Karen]

Duh! Thank you all. It's definitely been a painstakingly long day when I
can overlook such simplicity!