IF formula not working

[l_heaith]

I saw where you can have up to 64 nested formulas. I only have 15 and am getting an error. I'm pretty sure I did it correctly. Formula as follows:
=IF(C1="Abbott","2.75",IF(C1="Calabrio","2.75",IF(C1="Cellcom Repair","2.75",IF(C1="Cellcom Retail","2.75",IF(C1="Cellcom Service","2.75",IF(C1="Marvin","2.75",IF(C1="NCSS","2.75,IF(C1="USAA","2.75",IF(C1="Banking","3.00",IF(C1="Cellcom PO","3.00",IF(C1="Centra","3.00",IF(C1="HD Supply","3.00",IF(C1="HDS Order","3.00",IF(C1="CS PO","3.50",IF(C1="Ryder","4.00")))))))))))))))

I'm using Excel 2010. Any reason this isn't working? Thanks for any help!

 

[cyrilbrd]

Try this:
you forgot a " after IF(C1="NCSS","2.75",

	A	B	C	D	E	F	G
Abbott	Desc	Val
Abbott
If	Calabrio
Match	Cellcom Repair
Ceelcom retail
Cellcom Service
Marvin
USAA
Banking
Cellcom PO
Centra
HD Supply
HDS Order
CS PO
Ryder

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=F7E9D0"" target="_blank"></colgroup><colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3.5[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

</tbody>

★ l_heaith​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]B3[/TH]
[TD="align: left"]=IF(C1="Abbott","2.75",IF(C1="Calabrio","2.75",IF(C1="Cellcom Repair","2.75",IF(C1="Cellcom Retail","2.75",IF(C1="Cellcom Service","2.75",IF(C1="Marvin","2.75",IF(C1="NCSS","2.75",IF(C1="USAA","2.75",IF(C1="Banking","3.00",IF(C1="Cellcom PO","3.00",IF(C1="Centra","3.00",IF(C1="HD Supply","3.00",IF(C1="HDS Order","3.00",IF(C1="CS PO","3.50",IF(C1="Ryder","4.00")))))))))))))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: <a href="]B4[/TH]
[TD="align: left"]=INDEX(Table2[Val],MATCH(C1,Table2[Desc],0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

Remove the double-quotes around numbers if your intent is to use them as numeric values. For example, change "2.75" to 2.75.

With cyrilbrd's design, it is not necessary to use a table object and INDEX/MATCH.

Aside.... I avoid table objects because Excel imposes restrictions on their use, IIRC.

It is sufficient to write:

=VLOOKUP(C1, $F$2:$G$15, 2)

That presumes that C1 matches one of the strings in F2:F15.

Your original IF() formula returns FALSE if it does not. That is usually undesirable. But it was not clear if you merely overdesigned the IF() formula, or if you overlooked returning a meaningful value when C1 does not match any of the strings.

If the latter, use the following formula:

=IFERROR(VLOOKUP(C1, $F$2:$G$15, 2), "whatever you want")

That works for Excel 2007 and later; that is, Excel files that are not saved as "xls".

 

[l_heaith]

Try this:
you forgot a " after IF(C1="NCSS","2.75",

	A	B	C	D	E	F	G
Abbott	Desc	Val
Abbott
If	Calabrio
Match	Cellcom Repair
Ceelcom retail
Cellcom Service
Marvin
USAA
Banking
Cellcom PO
Centra
HD Supply
HDS Order
CS PO
Ryder

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.75[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3.5[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

</tbody>

★ l_heaith​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B3[/TH]
[TD="align: left"]=IF(C1="Abbott","2.75",IF(C1="Calabrio","2.75",IF(C1="Cellcom Repair","2.75",IF(C1="Cellcom Retail","2.75",IF(C1="Cellcom Service","2.75",IF(C1="Marvin","2.75",IF(C1="NCSS","2.75",IF(C1="USAA","2.75",IF(C1="Banking","3.00",IF(C1="Cellcom PO","3.00",IF(C1="Centra","3.00",IF(C1="HD Supply","3.00",IF(C1="HDS Order","3.00",IF(C1="CS PO","3.50",IF(C1="Ryder","4.00")))))))))))))))[/TD]
[/TR]
[TR]
[TH]B4[/TH]
[TD="align: left"]=INDEX(Table2[Val],MATCH(C1,Table2[Desc],0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you. Didn't see that I forgot a ". Will give that a try.

 

[l_heaith]

Thank you. I tried to do it that way to so I wouldn't have to write it all out but got an error message but will try this again! Thank you!

 

[l_heaith]

Thank you! I didn't catch that miss. Will give this a go again. Cheers!

 

[l_heaith]

I also forgot one crucial piece of info. I've been trying to write it and it's not working out so I don't know if it's possible in Excel. I'm trying to write if L2 is true then vlookup(C1, $F$2:$G$15, 2) if that makes sense. Thank you!