multiple nested IF's

S

silviu19772017

New Member
Joined
Mar 29, 2017
Messages
2
Hello guys,

I have a big problem... I am a newbie in Excel.
I made a big formula with multiple nested IF's and gives over 64 nested error.
This is the formula:

=IF(AND(P2="Lentile",ISNUMBER(SEARCH("112110",H:H))),"1.50 PL ALB",IF(AND(P2="Lentile",ISNUMBER(SEARCH("112120.7",H:H))),"1.50 PL ALB Panoramic",IF(AND(P2="Lentile",ISNUMBER(SEARCH("11211Z",H:H))),"ULTRA",IF(AND(P2="Lentile",ISNUMBER(SEARCH("11301V",H:H))),"BB ASP SHMC",IF(AND(P2="Lentile",ISNUMBER(SEARCH("11302V",H:H))),"BB ASP SHMC 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("112120.7",H:H))),"1.50 PL ALB Panoramic",IF(AND(P2="Lentile",ISNUMBER(SEARCH("112113.7",H:H))),"1.50 AR Panoramic",IF(AND(P2="Lentile",ISNUMBER(SEARCH("113114",H:H))),"1.56 EMI",IF(AND(P2="Lentile",ISNUMBER(SEARCH("113124",H:H))),"1.56 EMI 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("115019",H:H))),"1.61 SHMC VERDE",IF(AND(P2="Lentile",ISNUMBER(SEARCH("115069",H:H))),"1.61 SHMC VERDE 75",IF(AND(P2="Lentile",ISNUMBER(SEARCH("11502U",H:H))),"1.61 CRISTAL 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("11501U",H:H))),"1.61 SUPERHYDRO",IF(AND(P2="Lentile",ISNUMBER(SEARCH("124119",H:H))),"POLY SUPERHIDRO",IF(AND(P2="Lentile",ISNUMBER(SEARCH("124129",H:H))),"POLY SUPERHIDRO 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("215113",H:H))),"1.61 AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("215123",H:H))),"1.61 AR 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("331110",H:H))),"ST ALBA IT",IF(AND(P2="Lentile",ISNUMBER(SEARCH("331113",H:H))),"ST AR IT",IF(AND(P2="Lentile",ISNUMBER(SEARCH("331123",H:H))),"ST AR IT 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("331115",H:H))),"ST HM AR IT",IF(AND(P2="Lentile",ISNUMBER(SEARCH("331125",H:H))),"ST HM AR IT 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("331116",H:H))),"ST HG AR IT",IF(AND(P2="Lentile",ISNUMBER(SEARCH("33111N",H:H))),"ST HM IT",IF(AND(P2="Lentile",ISNUMBER(SEARCH("33111T",H:H))),"ST HG IT",IF(AND(P2="Lentile",ISNUMBER(SEARCH("431110",H:H))),"ST ALBA",IF(AND(P2="Lentile",ISNUMBER(SEARCH("431113",H:H))),"ST AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("431114",H:H))),"ST EMI",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C31115",H:H))),"ST HM AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C31116",H:H))),"ST HG AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C3111N",H:H))),"ST HM",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C3111T",H:H))),"ST HG",IF(AND(P2="Lentile",ISNUMBER(SEARCH("512112",H:H))),"1.50 TINTABLE",IF(AND(P2="Lentile",ISNUMBER(SEARCH("512122",H:H))),"1.50 TINTABLE 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("112220",H:H))),"1.50 BIF ALB",IF(AND(P2="Lentile",ISNUMBER(SEARCH("112223",H:H))),"1.50 BIF AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("113223",H:H))),"1.56 BIF AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("512123",H:H))),"1.499 AR 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("41311A",H:H))),"1.56 BLUE BLOCK",IF(AND(P2="Lentile",ISNUMBER(SEARCH("41312A",H:H))),"1.56 BLUE BLOCK 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("B13115",H:H))),"1.56 PL HM AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("B13116",H:H))),"1.56 PL HG AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("B13125",H:H))),"1.56 PL HM AR 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("B13126",H:H))),"1.56 PL HG AR 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("513119",H:H))),"1.56 SUPERHYDRO",IF(AND(P2="Lentile",ISNUMBER(SEARCH("D16013",H:H))),"1.67 AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("513129",H:H))),"1.56 SUPERHYDRO 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("D16023",H:H))),"1.67 AR 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("424229",H:H))),"BIF POLY AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("B13225",H:H))),"BIF HM AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("B13226",H:H))),"BIF HG AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C12127",H:H))),"PC MARO AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C12128",H:H))),"PC GRI AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C13225",H:H))),"BIF HM AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C13226",H:H))),"BIF HG AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("b1311s",H:H))),"pl h verde ar",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C13115",H:H))),"PL HM",IF(AND(P2="Lentile",ISNUMBER(SEARCH("B1312S",H:H))),"pl h verde ar 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C13125",H:H))),"PL HM 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C13116",H:H))),"PLHG",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C13126",H:H))),"PLHG 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("41216P",H:H))),"POLAR MARO 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("41216Q",H:H))),"POLAR GRI 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("41216R",H:H))),"POLAR VERDE 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("424113",H:H))),"poly",IF(AND(P2="Lentile",ISNUMBER(SEARCH("424115",H:H))),"POLY HM AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("424116",H:H))),"POLY HG AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("424123",H:H))),"POLY AR 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("424125",H:H))),"POLY HM AR 70",IF(AND(P2="Lentile",ISNUMBER(SEARCH("c12710",H:H))),"PRISMA",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C12713",H:H))),"PRISMA AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C32710",H:H))),"PRISMA STICLA",IF(AND(P2="Lentile",ISNUMBER(SEARCH("113323",H:H))),"PROG AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("513423",H:H))),"PROG FREEFORM AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("B13325",H:H))),"PROG HM AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("B13326",H:H))),"PROGR HG AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C13325",H:H))),"PROG HM AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("C13326",H:H))),"PROGR HG AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("112113.7",H:H))),"1.50 AR Panoramic",IF(AND(P2="Lentile",ISNUMBER(SEARCH("112113",H:H))),"1.50 AR",IF(AND(P2="Lentile",ISNUMBER(SEARCH("112123",H:H))),"1.50 AR 70",IF(AND(P2="Lentile",OR(H2="112120.2",H2="112120.5",H2="112120.6",H2="112120.20",H2="112120.21")),"1.50 PL ALB 70", "X"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

I know is ugly ...
The bottom line is that i check inside a table like this:
One column has the product type "P2"
One column "H:H" has product code
It searches thgrough this columns and gives the product type.
Is there any other solution ?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Ouch, it sure got me dizzy. You need to put your Product Code-Product Type pairing in a table so that you can just do a lookup.
 
Upvote 0
I think this will help. Place your Product Code-Product Type pairing in Columns A and B ex.
dmvaxd.png


And then you can do
Code: 
=IF(P2="Lentile",VLOOKUP(H1,"A:B",2,0),"")

This is just a quick sample solution. You can have the pairing in a different column and you just need to change your formula accordingly.

If this post is helpful, feel free to Like my response. Thank you.
 
Upvote 0
Here's the table (thank heavens for macros!)

Code: 
"112110","1.50 PL ALB"
"112120.7","1.50 PL ALB Panoramic"
"11211Z","ULTRA"
"11301V","BB ASP SHMC"
"11302V","BB ASP SHMC 70,
"112120.7","1.50 PL ALB Panoramic"
"112113.7","1.50 AR Panoramic"
"113114","1.56 EMI"
"113124","1.56 EMI 70"
"115019","1.61 SHMC VERDE"
"115069","1.61 SHMC VERDE 75"
"11502U","1.61 CRISTAL 70"
"11501U","1.61 SUPERHYDRO"
"124119","POLY SUPERHIDRO"
"124129","POLY SUPERHIDRO 70"
"215113","1.61 AR"
"215123","1.61 AR 70"
"331110","ST ALBA IT"
"331113","ST AR IT"
"331123","ST AR IT 70"
"331115","ST HM AR IT"
"331125","ST HM AR IT 70"
"331116","ST HG AR IT"
"33111N","ST HM IT"
"33111T","ST HG IT"
"431110","ST ALBA"
"431113","ST AR"
"431114","ST EMI"
"C31115","ST HM AR"
"C31116","ST HG AR"
"C3111N","ST HM"
"C3111T","ST HG"
"512112","1.50 TINTABLE"
"512122","1.50 TINTABLE 70"
"112220","1.50 BIF ALB"
"112223","1.50 BIF AR"
"113223","1.56 BIF AR"
"512123","1.499 AR 70"
"41311A","1.56 BLUE BLOCK"
"41312A","1.56 BLUE BLOCK 70"
"B13115","1.56 PL HM AR"
"B13116","1.56 PL HG AR"
"B13125","1.56 PL HM AR 70"
"B13126","1.56 PL HG AR 70"
"513119","1.56 SUPERHYDRO"
"D16013","1.67 AR"
"513129","1.56 SUPERHYDRO 70"
"D16023","1.67 AR 70"
"424229","BIF POLY AR"
"B13225","BIF HM AR"
"B13226","BIF HG AR"
"C12127","PC MARO AR"
"C12128","PC GRI AR"
"C13225","BIF HM AR"
"C13226","BIF HG AR"
"b1311s","pl h verde ar"
"C13115","PL HM"
"B1312S","pl h verde ar 70"
"C13125","PL HM 70"
"C13116","PLHG"
"C13126","PLHG 70"
"41216P","POLAR MARO 70"
"41216Q","POLAR GRI 70"
"41216R","POLAR VERDE 70"
"424113","poly"
"424115","POLY HM AR"
"424116","POLY HG AR"
"424123","POLY AR 70"
"424125","POLY HM AR 70"
"c12710","PRISMA"
"C12713","PRISMA AR"
"C32710","PRISMA STICLA"
"113323","PROG AR"
"513423","PROG FREEFORM AR"
"B13325","PROG HM AR"
"B13326","PROGR HG AR"
"C13325","PROG HM AR"
"C13326","PROGR HG AR"
"112113.7","1.50 AR Panoramic"
"112113","1.50 AR"
"112123","1.50 AR 70"

Only part not in the table is this end bit

IF(AND(P2="Lentile",OR(H2="112120.2",H2="112120.5",H2="112120.6",H2="112120.20",H2="112120.21")),"1.50 PL ALB 70", "X"
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,634
Messages
6,125,938
Members
449,275
Latest member
jacob_mcbride

Share this page

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