IF Function more than 64 levels of nesting

[Ifthisthanthat]

Hi all, newbie here, don't know what else to use besides IF function for this. Reaching nesting limit doing it this way. Any alternative ideas are appreciated!

Formula in B5 of image attached:

=IF((AND(C2=1.5,D2="L",H2=0.625)),'[1-MASTER.xls]A & LH SINGLE ROD END'!$M$10, (isolating this on one line so you can see it better)

IF((AND(C2=1.5,D2="L",H2=1)),'[1-MASTER.xls]A & LH SINGLE ROD END'!$M$11,IF((AND(C2=2,D2="L",H2=0.625)),'[1-MASTER.xls]A & LH SINGLE ROD END'!$M$12,IF((AND(C2=2,D2="L",H2=1)),'[1-MASTER.xls]A & LH SINGLE ROD END'!$M$13,IF((AND(C2=2,D2="L",H2=1.375)),'[1-MASTER.xls]A & LH SINGLE ROD END'!$M$14,IF((AND(C2=2.5,D2="L",H2=0.625)),'[1-MASTER.xls]A & LH SINGLE ROD END'!$M$15,IF((AND(C2=2.5,D2="L",H2=1)),'[1-MASTER.xls]A & LH SINGLE ROD END'!$M$16,IF((AND(C2=2.5,D2="L",H2=1.375)),'[1-MASTER.xls]A & LH SINGLE ROD END'!$M$17,IF((AND(C2=2.5,D2="L",H2=1.75)),'[1-MASTER.xls]A & LH SINGLE ROD, etc...., "ERROR")))

Logic in English, shown in "data" image attached: if cylinder's bore is 1.5", mount is "L" (or tie rod-column M), and rod diameter is .625", then "base price" is $223 (or $M$11).

In my case, for each mounting style (like the L mount), I have approximately 70 individual IF statements. From the sheet, you can see a lot of other mounting styles existing in the columns to the right of the "L" mounting style. I was going to do individual spreadsheets/pricing tools for each mounting style. Unless, whatever solution is suggested can accommodate much more date (IE approximately 70 individual statements multiplied by 16 mounting styles).

Thanks for any and all help!!!!!

 

[Ifthisthanthat]

 

[Ifthisthanthat]

I tried linking 2 images referred to in the initial post but I'm not seeing either image!

 

[Joe4]

There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.

Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[jtakw]

Hi,

More than likely, with that many possible combinations, building a Table containing all the data, then use a much simpler formula to reference that Table for a result is the way to go. Probably end up using something like VLOOKUP, HLOOKUP, INDEX/MATCH, SUMPRODUCT, etc. after building the Table.

 

[Joe4]

If it was me, I might build my own User Defined Function to do something that complex.
I think they are so much easier to read and maintain that some monstrous, complex formula.
Of course, you have to be able to use VBA to go that route.

 

[Ifthisthanthat]

Joe4... any good route to go out for hire on building a VBA tool like that? I don't know where to find a reliable Excel expert, but we would be willing to hire one for this project. Thanks.

 

[Joe4]

You can check out the "Excel Consulting Services Link" in the menus: https://www.mrexcel.com/consulting-services/