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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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
63,406
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,277
Members
430,536
Latest member
Manoj Gaidhankar

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
Top