Work around for nested IF limit

**damiensdad** · 04-16-2013, 09:51 AM

I am looking for an alternative formula to get around the 7 nested IF limit. This is the formula that I am using: "=IF(ISNUMBER(SEARCH("WARE",F10)),G10-(30/60/24),IF(ISNUMBER(SEARCH("PALMER",F10)),G10-(30/60/24),IF(ISNUMBER(SEARCH("AMHERST",F10)),G10-(45/60/24),IF(ISNUMBER(SEARCH("BELCHERTOWN",F10)),G10-(30/60/24),IF(ISNUMBER(SEARCH("CHICOPEE",F10)),G10-(45/60/24),IF(ISNUMBER(SEARCH("EAST LONGMEADOW",F10)),G10-(60/60/24)))))))." As you can see it is looking for town names in a cell and performing a time calc based on the town. My issue is that I need to extend this formula to about 25 more towns. Any help would be greatly appreciated.

**Fotis1991** · 04-16-2013, 10:01 AM

One way is using & and not comma between if. This has a limit of 30!

"=IF(ISNUMBER(SEARCH("WARE",F10)),G10-(30/60/24)&IF(ISNUMBER(SEARCH("PALMER",F10)),G10-(30/60/24)&if....

**Jonmo1** · 04-16-2013, 10:02 AM

It appears that the 30 45 60 is the only part that changes in the basic calculation
G10-(45/60/24)

You can creaate a lookup table of all the towns and their corresponding number (30 or 45 or 60)

So for the given example
Say

M1:N6
M1:M6 = the town names
N1:N6 = the corresponding number for that town. 30 45 or 60

Then use

=G10-(LOOKUP(2^15,SEARCH($M$1:$M$6,F10),$N$1:$N$6)/60/24)

**Tony Valko** · 04-16-2013, 10:03 AM

Create a 2 column table with the town names in the left column and the already calculated value in the right column.

Then you can use some form of a lookup formula.

You're using the SEARCH function in your current formula. Does that mean cell F10 may contain additional text including the town name?

**damiensdad** · 04-16-2013, 11:54 AM

Biff, yes the cell F10 contains an entire address and I am pulling out only the town.

**damiensdad** · 04-16-2013, 12:14 PM

Replacing the comma with "&" returns a value of "FALSE" as opposed to the time value that is returned with the comma. Am I missing something?

**Fotis1991** · 04-16-2013, 12:19 PM

http://spreadsheetpage.com/index.php..._if_functions/

But be sure that if you upload a sample workbook you'll find your solution too soon...

**Tony Valko** · 04-16-2013, 04:07 PM

Try this...

Create this table in the range A2:B7.

WARE.........................0:30
PALMER......................0:30
AMHERST....................0:45
BELCHERTOWN.............0:30
CHICOPEE...................0:45
EAST LONGMEADOW.....1:00

Then, the formula would be:

=G10-LOOKUP(1E100,SEARCH(A2:A7,F10),B2:B7)