Only first part of nested IF formula is working

[centurymantra]

I am using a nested IF formula as such:

=IF(A1="7eng.com","7G Enterprises LLC",if(a1=”a4gov.org”,”City of Ann Green”,if(a1=”abonaire.com”,”Abonaire Consultants”,” “)))

Dragging this formula down the column, it will work and populate a cell with "7G Enterprises, LLC" if it meets the first criteria, but just gives me a #NAME? error for anything else.

Can someone tell me what is missing from this formula?

Thanks!

 

[FormR]

=IF(A1="7eng.com","7G Enterprises LLC",if(a1=”a4gov.org”,”City of Ann Green”,if(a1=”abonaire.com”,”Abonaire Consultants”,” “)))

Hi, the red slanted quotes do not look correct - try replacing (retyping) them with normal quote marks "

 

[jmacleary]

Aha. This is a really subtle one - the answer is that in the rest of your formula, open and close quotes appear rather than double quotes - excel is remarkably picky about this sort of thing. Try:

=IF(A1="7eng.com","7G Enterprises LLC",IF(A1="a4gov.org","City of Ann Green",IF(A1="abonaire.com","Abonaire Consultants"," ")))

Cheers, John

 

[centurymantra]

Hi, the red slanted quotes do not look correct - try replacing (retyping) them with normal quote marks "

Interesting. That was the issue. I was typing the formula in Word and it displays quotation marks like that when in brackets. Any way to stop Word from doing that? This formula is going to have about 40 nested ifs and I'd prefer not having to go through and do this if possible.

 

[jmacleary]

Yes. In word options select proofing, then 'autocorrect options' then 'autoformat as you type' and remove the tick box next to 'straight quotes with smart quotes'. (the detail may be different depending on which word you are using - this is ok for word 2007. (I know - dinosaur but it works for me!).

 

[FormR]

This formula is going to have about 40 nested ifs

Hi, it sounds to me like you would be better of using a lookup table and a VLOOKUP() formula - much easier to write, maintain and troubleshoot. For example:

Excel 2013/2016
	A	B	C	D	E
1	a4gov.org	City of Ann Green		Lookup	Return
2				7end.com	7G Enterprises LLC
3				a4gov.org	City of Ann Green
4				abonaire.com	Abonaire Consultants
Sheet1

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,D1:E4,2,0)

 

[centurymantra]

Hi, it sounds to me like you would be better of using a lookup table and a VLOOKUP() formula - much easier to write, maintain and troubleshoot. For example:

Excel 2013/2016

	A	B	C	D	E
a4gov.org	Lookup	Return
7end.com	7G Enterprises LLC
a4gov.org	City of Ann Green
abonaire.com	Abonaire Consultants

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FFFF00"]City of Ann Green[/TD]
[TD="align: right"][/TD]

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

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

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=VLOOKUP(A1,D1:E4,2,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes...definitely. I was talking with someone else here at work and this was brought up. Thanks for the tip!