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

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
Rather than doing an IF statement, could you set up a table? Then you could just use a VLOOKUP.
 

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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
 

jpm021990

New Member
Joined
Sep 11, 2014
Messages
11

ADVERTISEMENT

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,430
Members
409,876
Latest member
Akash Yadav
Top