Ive created a monster and its too big

[jpm021990]

As the title says, is there any way to simplify this? I get the error that I have more than 64 levels of nesting

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

 

[bbott]

Rather than doing an IF statement, could you set up a table? Then you could just use a VLOOKUP.

 

[jpm021990]

Rather than doing an IF statement, could you set up a table? Then you could just use a VLOOKUP.

I'm unfamiliar with vlookup, what would an example look like?

 

[bbott]

Here's a small example using your data. This table is in A1:B3.

BF-O	3
BF-W	1.9
BF-S	1.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]

Here's a small example using your data. This table is in A1:B3.

BF-O	3
BF-W	1.9
BF-S	1.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

 

[jpm021990]

Does anyone know what im trying to do?

 

[Joe4]

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