Ive created a monster and its too big

jpm021990

New Member
Joined
Sep 11, 2014
Messages
11
As the title says, is there any way to simplify this? I get the error that I have more than 64 levels of nesting

Code:
=IF(ISNUMBER(SEARCH("BF-O",A:A)),"3",IF(ISNUMBER(SEARCH("BF-W",A:A)),"1.9",IF(ISNUMBER(SEARCH("BF-S",A:A)),"1.1",IF(ISNUMBER(SEARCH("BF-R",A:A)),"2.4",IF(ISNUMBER(SEARCH("BF-H",A:A)),"2",IF(ISNUMBER(SEARCH("BF-T",A:A)),".4",IF(ISNUMBER(SEARCH("BF-RHS",A:A)),".9",IF(ISNUMBER(SEARCH("BF-ED",A:A)),"1.3",IF(ISNUMBER(SEARCH("T-BF",A:A)),".4",IF(ISNUMBER(SEARCH("T-S",A:A)),"1.2",IF(ISNUMBER(SEARCH("T-H",A:A)),"2.2",IF(ISNUMBER(SEARCH("T-W",A:A)),"1.8",IF(ISNUMBER(SEARCH("T-R",A:A)),"2.3",,IF(ISNUMBER(SEARCH("T-O",A:A)),"2.7",IF(ISNUMBER(SEARCH("T-GW",A:A)),"2.3",,IF(ISNUMBER(SEARCH("BF-GW",A:A)),"2.4",IF(ISNUMBER(SEARCH("T-ED",A:A)),"1.1",IF(ISNUMBER(SEARCH("T-RHS",A:A)),"1.0",IF(ISNUMBER(SEARCH("S-BF",A:A)),"1.1",IF(ISNUMBER(SEARCH("S-T",A:A)),"1.2",IF(ISNUMBER(SEARCH("S-H",A:A)),"1.1",IF(ISNUMBER(SEARCH("S-RHS",A:A)),".4",IF(ISNUMBER(SEARCH("S-ED",A:A)),"1.0",IF(ISNUMBER(SEARCH("S-W",A:A)),"3.0",IF(ISNUMBER(SEARCH("S-R",A:A)),"2.1",IF(ISNUMBER(SEARCH("S-GW",A:A)),"1.8",IF(ISNUMBER(SEARCH("S-O",A:A)),"2.3",,IF(ISNUMBER(SEARCH("H-S",A:A)),"1.1",IF(ISNUMBER(SEARCH("H-T",A:A)),"2.2",IF(ISNUMBER(SEARCH("H-BF",A:A)),"2",IF(ISNUMBER(SEARCH("H-RHS",A:A)),"1.4",IF(ISNUMBER(SEARCH("H-ED",A:A)),"2.0",IF(ISNUMBER(SEARCH("H-W",A:A)),"3.9",IF(ISNUMBER(SEARCH("H-R",A:A)),"3.2",IF(ISNUMBER(SEARCH("H-GW",A:A)),"3.1",IF(ISNUMBER(SEARCH("H-O",A:A)),"2.6",IF(ISNUMBER(SEARCH("RHS-BF",A:A)),".9",IF(ISNUMBER(SEARCH("RHS-T",A:A)),"1.0",IF(ISNUMBER(SEARCH("RHS-S",A:A)),".4",IF(ISNUMBER(SEARCH("RHS-H",A:A)),"1.4",IF(ISNUMBER(SEARCH("RHS-W",A:A)),"2.4",IF(ISNUMBER(SEARCH("RHS-ED",A:A)),".6",IF(ISNUMBER(SEARCH("RHS-R",A:A)),"1.7",IF(ISNUMBER(SEARCH("RHS-GW",A:A)),"1.4",IF(ISNUMBER(SEARCH("RHS-O",A:A)),"1.7",IF(ISNUMBER(SEARCH("ED-BF",A:A)),"1.3",IF(ISNUMBER(SEARCH("ED-T",A:A)),"1.1",IF(ISNUMBER(SEARCH("ED-S",A:A)),"1.0",IF(ISNUMBER(SEARCH("ED-H",A:A)),"2.0",IF(ISNUMBER(SEARCH("ED-RHS",A:A)),".6",IF(ISNUMBER(SEARCH("ED-W",A:A)),"2.0",IF(ISNUMBER(SEARCH("ED-R",A:A)),"1.1",IF(ISNUMBER(SEARCH("ED-GW",A:A)),".8",IF(ISNUMBER(SEARCH("ED-O",A:A)),"1.4",IF(ISNUMBER(SEARCH("W-BF",A:A)),"1.9",IF(ISNUMBER(SEARCH("W-T",A:A)),"1.8",IF(ISNUMBER(SEARCH("W-S",A:A)),"3.0",IF(ISNUMBER(SEARCH("W-H",A:A)),"3.9",IF(ISNUMBER(SEARCH("W-RHS",A:A)),"2.4",IF(ISNUMBER(SEARCH("W-ED",A:A)),"2.0",IF(ISNUMBER(SEARCH("W-R",A:A)),"1.3",IF(ISNUMBER(SEARCH("W-GW",A:A)),"1.3",IF(ISNUMBER(SEARCH("W-O",A:A)),"2.0",IF(ISNUMBER(SEARCH("R-BF",A:A)),"2.4",IF(ISNUMBER(SEARCH("R-T",A:A)),"2.3",IF(ISNUMBER(SEARCH("R-S",A:A)),"2.1",IF(ISNUMBER(SEARCH("R-H",A:A)),"3.2",IF(ISNUMBER(SEARCH("R-RHS",A:A)),"1.7",IF(ISNUMBER(SEARCH("R-ED",A:A)),"1.1",IF(ISNUMBER(SEARCH("R-W",A:A)),"1.3",IF(ISNUMBER(SEARCH("R-GW",A:A)),".4",IF(ISNUMBER(SEARCH("R-O",A:A)),"1.1",IF(ISNUMBER(SEARCH("GW-BF",A:A)),"2.4",IF(ISNUMBER(SEARCH("GW-T",A:A)),"2.3",IF(ISNUMBER(SEARCH("GW-S",A:A)),".1.8",IF(ISNUMBER(SEARCH("GW-H",A:A)),"3.1",IF(ISNUMBER(SEARCH("GW-RHS",A:A)),"1.4",IF(ISNUMBER(SEARCH("GW-ED",A:A)),".8",IF(ISNUMBER(SEARCH("GW-W",A:A)),"1.3",IF(ISNUMBER(SEARCH("GW-R",A:A)),".4",IF(ISNUMBER(SEARCH("GW-O",A:A)),".7",IF(ISNUMBER(SEARCH("O-BF",A:A)),"3",IF(ISNUMBER(SEARCH("O-T",A:A)),"2.7",IF(ISNUMBER(SEARCH("O-S",A:A)),"2.3",IF(ISNUMBER(SEARCH("O-H",A:A)),"2.6",IF(ISNUMBER(SEARCH("O-RHS",A:A)),"1.7",IF(ISNUMBER(SEARCH("O-ED",A:A)),"1.4",IF(ISNUMBER(SEARCH("O-W",A:A)),"2.0",IF(ISNUMBER(SEARCH("O-R",A:A)),"1.1",IF(ISNUMBER(SEARCH("O-GW",A:A)),".7"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Rather than doing an IF statement, could you set up a table? Then you could just use a VLOOKUP.
 
Upvote 0
Here's a small example using your data. This table is in A1:B3.

BF-O3
BF-W1.9
BF-S1.1

<tbody>
</tbody>

Using this formula, it will return 1.9 because it is looking for BF-W.

=VLOOKUP("BF-W",A1:B3,2,0)

You can change the formula to look at a reference cell. For example, if you wanted to look up BF-S, enter it in D1 and this formula in E1:

=VLOOKUP(D1,A1:B3,2,0)

and it will return 1.1
 
Upvote 0
Here's a small example using your data. This table is in A1:B3.

BF-O3
BF-W1.9
BF-S1.1

<tbody>
</tbody>

Using this formula, it will return 1.9 because it is looking for BF-W.

=VLOOKUP("BF-W",A1:B3,2,0)

You can change the formula to look at a reference cell. For example, if you wanted to look up BF-S, enter it in D1 and this formula in E1:

=VLOOKUP(D1,A1:B3,2,0)

and it will return 1.1

will this work if I'm not sure what will be typed in Column A?

For example, I might have
BF-W
W-R
BF-S
S-ED

I want the program to automatically input the correct result next to each one in Column B
 
Upvote 0
Yes, and VLOOKUP should do exactly what you want.
Take a look at Excel's built-in help files on the VLOOKUP function. Excel's help files on functions are excellent. The have complete descriptions and examples.
There are also many links you can find on Google which also show explanations and examples, like these:
MS Excel: VLOOKUP Function (WS)
VLOOKUP - Excel
How to use VLOOKUP in Excel
 
Upvote 0

Forum statistics

Threads
1,216,099
Messages
6,128,822
Members
449,469
Latest member
Kingwi11y

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top