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,FA LSE),
IF(C11="Aircard",VLOOKUP(A11,Sheet1!$A$2:$G$32,8,F ALSE)," ")))

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.
--
Thanks, Karen

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


"Karen" wrote:

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,FA LSE),
IF(C11="Aircard",VLOOKUP(A11,Sheet1!$A$2:$G$32,8,F ALSE)," ")))

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.
--
Thanks, Karen

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,FA LSE),
IF(C11="Aircard",VLOOKUP(A11,Sheet1!$A$2:$G$32,8,F ALSE)," ")))

=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),"")


--
John C


"Karen" wrote:

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,FA LSE),
IF(C11="Aircard",VLOOKUP(A11,Sheet1!$A$2:$G$32,8,F ALSE)," ")))

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.
--
Thanks, Karen

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" wrote:

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,FA LSE),
IF(C11="Aircard",VLOOKUP(A11,Sheet1!$A$2:$G$32,8,F ALSE)," ")))

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.
--
Thanks, Karen

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


"Mike H" wrote:

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" wrote:

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,FA LSE),
IF(C11="Aircard",VLOOKUP(A11,Sheet1!$A$2:$G$32,8,F ALSE)," ")))

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.
--
Thanks, Karen

Just to add...


=IF(C11="Cingular",....Sheet1!$A$2:$G$23,7,FALSE),
IF(C11="Nextel",... ,Sheet1!$A$2:$G$32,6,FALSE


Is the Cingular Lookup table to row 23, or 32. (looks like a typo)

If they are the same, perhaps consider a range name for your table.

=IF(C11="Cingular",VLOOKUP(A11,Tbl,7,FALSE), etc...

--
Dana DeLouis


"Karen" wrote in message ...

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,FA LSE),
IF(C11="Aircard",VLOOKUP(A11,Sheet1!$A$2:$G$32,8,F ALSE)," ")))

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.
--
Thanks, Karen