¨...more than 64 levels of nesting¨ Help?

Good afternoon guys,
I have just been introduced to this forum, and couldn´t wait to post my first thread (yey...)

I am not experienced with excel, therefore please take me slow...
I am trying to create a ¨calculator¨ that would be able to analyze a pretty big table, then provide the most efficient cost out of that table, taking into consideration the origin and destination of the freight. For example:
**NOTE THAT IT IS SET UP IN SPANISH, SO THE FUNCTIONS SOUND A LITTLE FUNNY.

=SI(Y($AB$5=D4;$E$103<=$E$3);E4;SI(Y($AB$5=D4;$E$103<=$F$3);F4;SI(Y($AB$5=D4;$E$103<=$G$3);G4;SI(Y($AB$5=D4;$E$103<=$H$3);H4;SI(Y($AB$5=D4;$E$103<=$I$3);I4;SI(Y($AB$5=D4;$E$103<=$J$3);J4;SI(Y($AB$5=D4;$E$103<=$K$3);K4;SI(Y($AB$5=D4;$E$103<=$L$3);L4;SI(Y($AB$5=D4;$E$103<=$M$3);M4;SI(Y($AB$5=D4;$E$103<=$N$3);N4;SI(Y($AB$5=D4;$E$103<=$O$3);O4;SI(Y($AB$5=D4;$E$103<=$P$3);P4;SI(Y($AB$5=D4;$E$103<=$Q$3);Q4;SI(Y($AB$5=D4;$E$103<=$R$3);R4;SI(Y($AB$5=D4;$E$103<=$S$3);S4;SI(Y($AB$5=D4;$E$103<=$T$3);T4;SI(Y($AB$5=D4;$E$103<=$U$3);U4;SI(Y($AB$5=D4;$E$103<=$V$3);V4;SI(Y($AB$5=D4;$E$103<=$W$3);W4;¨¨)))))))))))))))))))

So this is the formula for the first row. If the Name in AB5(calc) = the name in D4(actual table), then it needs to see what range the Kilos are falling in, being no less than 19 categories. If the weight is <= to 250, 500, 750...
Now, each row requires more or less the same formula, so it can look for the criteria in AB5 all over the table... As you probably already know, it doesn t allow me to se more than 64 levels of nesting, reason for which I have posted this thread...
Could anyone help?
Thank you all for your time and assistance, looking forward to hearing from you

Attach a sample workbook. (Not a picture!)
Make sure there is just enough data to demonstrate your need. Include a BEFORE (original) sheet and an AFTER (required output) sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary. Remember, it should reflect original structure of your data.
Remember to desensitize the data.
Note:

• Please do not attach password protected workbooks/worksheets
• Please do not attach file(s) from exterior servers
• Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

20 rows of data is enough (probably)

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.

Thank you, sandy666 for your reply.
I hope I have attached the worksheet in this reply. I am not sure how that works

btw. your last parameter is wrong: ¨¨))))))))))))))))))). Should be "")))))))))))))))))))

Formula:

Please Login or Register  to view this content.

See attached:

try

=INDEX($E$4:$W$94,MATCH(Z16,$D$4:$D$93,0),MATCH(Z18,$E$3:$W$3,1))

Row 3 headings changed from E3.

the first range equates <=249

If you want it to be <=250, then add 1 to all other range i.e. 251,501,751 etc

Thank you, JohnTopley, for your proposition. It is working successfully, thanks to you.
I will come back to this thread, if allowed, with some deeper functions.
Thank you all for your assistance

Teodor

Hello again,

Now that is is working somehow, here comes a much disturbing question:
If the table has more results for one category, would it be possible for the calculator to show all the options? or compare them and make a top 3?

Can you please post a file illustrating the query in your last post re more results for one category(#8).

Good morning, guys, and thank you for replying to my thread.
As requested, I have attached the same spreadsheet, however in the AFTER sheet, I have highlighted three of the rows.
In this case, as we have the same match (Avila) but provides three suppliers with three different offers, is there any possibility for the ¨calculator¨
to display all three options? or can I tell excel how to judge the options, and give my boss the option I think is best?

Thank you in advance for all your time and efforts put into this, looking forward to hearing from you.

Teo.

Try

in Z20

=MIN(OFFSET($D$3,MATCH($Z$16,$D$4:$D$93,0),MATCH(Z18,$E$3:$W$3,1),COUNTIF(D:D,$Z$16)))

Assumes the data is sorted by Destination i.e AVILA are sequential.

NOTE: Drop down for "AVILA" had a trailing blank so was giving wrong match so make sure all entries in D and the drop down are correct.

Thank you, John!! It is working, however i am left in big doubt.
How is this formula deciding between the three columns? What if we merge the three ¨Aliva¨s, and have one heading with three options?
Could the formula work with that?

NO! NO! You cannot (nor should you!) merge cells (ever!) as the formula works by COUNTING the number of AVILA entries: if you merge cells, then there is no mechanism for determining how many entries there are for a given supplier/destination.

It makes sense, I looked up each function to see the purpose and now i can relate to your explanation.
I am still trying to build on it, however i owe it all to you.
I will for sure come back to this thread at some point, however I will try to take up as little of your time as possible.

Best regards,
Teo.

No problem for me if you need further help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Good morning, John!!

I have yet another speedbump I can´t get miself over...
As you will be able to observe in the attached file-before sheet-, i have added some x & y´s. How can I make the little calculatorç
also return whether if the resulted price belongs to an x or a y row? or would it be easier to make use of the colors of the rows?

Thank you in advance for your time and assistance!!

Teo.

In AB20 ( "helper" to identify which of the "Avila" rows the MIN is found)

=MATCH(MIN(OFFSET($D$3,MATCH($Z$16,$D$4:$D$93,0),MATCH($Z$18,$E$3:$W$3,1),COUNTIF($D:$D,$Z$16))),OFFSET($D$3,MATCH($Z$16,$D$4:$D$93,0),MATCH($Z$18,$E$3:$W$3,1),COUNTIF($D:$D,$Z$16)),0)

in AA (obtains X or y from column C)

=INDEX($C$4:$C$93,MATCH($Z$16,$D$4:$D$93,0)+$AB$20-1)

But did not understand

little calculators

Good morning, dear John!!
I hope this message finds you well

I have been struggling with this spreadsheet over the last two weeks, however I came to a point in which I am stuck again...
I have attached the sample spreadsheet for you to understand what I m dreaming of here...

So idk if you remember the calculator that you´ve pretty much built for me in this thread.. but it got more complex, having many more details that the calculator has to base it´s answers on...
There are now several suppliers that are offering the same destinations, but at a slightly different price. there are, as well, two final destinations, according to which the final price is calculated.
Right now, I have made two calculators, one for each final destination, however I am hoping that there is a way for combining the two into one table that sorts everything out by itself based on the huge table. Would it be possible?

Thank you for your time and assistance, looking forward to hearing from you

Teo.

Need for more than 64 levels of nested - Simplify formula -
Here's is my formula, its for a quote, for cleaning business,
For type of cleaning: Standard, Deep or Complete, with the selection of how many Square foot, and the price..
It's a very long one and hope there's someone can help me simplify it. Thanks in advance!

=IF(AND(B7="Standard",C8=2000,E7="1X a week"),I9,IF(AND(B7="Standard",C8=2500,E7="1X a week"),I10,
IF(AND(B7="Standard",C8=3000,E7="1X a week"),I11,IF(AND(B7="Standard",C8=3500,E7="1X a week"),I12,
IF(AND(B7="Standard",C8=4000,E7="1X a week"),I13,IF(AND(B7="Standard",C8=4500,E7="1X a week"),I14,
IF(AND(B7="Standard",C8=5000,E7="1X a week"),I15,IF(AND(B7="Standard",C8=5500,E7="1X a week"),I16,
IF(AND(B7="Standard",C8=6000,E7="1X a week"),I17,IF(AND(B7="Standard",C8=6500,E7="1X a week"),I18,
IF(AND(B7="Standard",C8=7000,E7="1X a week"),I19,IF(AND(B7="Standard",C8=7500,E7="1X a week"),I20,
IF(AND(B7="Standard",C8=8000,E7="1X a week"),I21,IF(AND(B7="Standard",C8=8500,E7="1X a week"),I21,
IF(AND(B7="Standard",C8=9000,E7="1X a week"),I22,IF(AND(B7="Standard",C8=9500,E7="1X a week"),I23,
IF(AND(B7="Standard",C8=10000,E7="1X a week"),I24,IF(AND(B7="Standard",C8=10500,E7="1X a week"),I25,
IF(AND(B7="Standard",C8=11000,E7="1X a week"),I26,IF(AND(B7="Standard",C8=11500,E7="1X a week"),I27,
IF(AND(B7="Standard",C8=12000,E7="1X a week"),I28,IF(AND(B7="Standard",C8=12500,E7="1X a week"),I29,
IF(AND(B7="Standard",C8=13000,E7="1X a week"),I30,IF(AND(B7="Standard",C8=13500,E7="1X a week"),I31,
IF(AND(B7="Standard",C8=14000,E7="1X a week"),I32,IF(AND(B7="Standard",C8=14500,E7="1X a week"),I33,
IF(AND(B7="Standard",C8=15000,E7="1X a week"),I33,IF(AND(B7="Standard",C8=15500,E7="1X a week"),I34,
IF(AND(B7="Standard",C8=15500,E7="1X a week"),I35,IF(AND(B7="Standard",C8=16000,E7="1X a week"),I36,
IF(AND(B7="Standard",C8=16500,E7="1X a week"),I37,IF(AND(B7="Standard",C8=17000,E7="1X a week"),I38,
IF(AND(B7="Standard",C8=17500,E7="1X a week"),I39,IF(AND(B7="Standard",C8=18000,E7="1X a week"),I40,
IF(AND(B7="Standard",C8=18500,E7="1X a week"),I41,IF(AND(B7="Standard",C8=19000,E7="1X a week"),I42,
IF(AND(B7="Standard",C8=19500,E7="1X a week"),I43,IF(AND(B7="Standard",C8=20000,E7="1X a week"),I44,
IF(AND(B7="Deep",C8=2000,E7="1X a week"),J9,IF(AND(B7="Deep",C8=2500,E7="1X a week"),J10,
IF(AND(B7="Deep",C8=3000,E7="1X a week"),J11,IF(AND(B7="Deep",C8=3500,E7="1X a week"),J12,
IF(AND(B7="Deep",C8=4000,E7="1X a week"),J13,IF(AND(B7="Deep",C8=4500,E7="1X a week"),J14,
IF(AND(B7="Deep",C8=5000,E7="1X a week"),J15,IF(AND(B7="Deep",C8=5500,E7="1X a week"),J16,
IF(AND(B7="Deep",C8=6000,E7="1X a week"),J17,IF(AND(B7="Deep",C8=6500,E7="1X a week"),J18,
IF(AND(B7="Deep",C8=7000,E7="1X a week"),J19,IF(AND(B7="Deep",C8=7500,E7="1X a week"),J20,
IF(AND(B7="Deep",C8=8000,E7="1X a week"),J21,IF(AND(B7="Deep",C8=8500,E7="1X a week"),J21,
IF(AND(B7="Deep",C8=9000,E7="1X a week"),J22,IF(AND(B7="Deep",C8=9500,E7="1X a week"),J23,
IF(AND(B7="Deep",C8=10000,E7="1X a week"),J24,IF(AND(B7="Deep",C8=10500,E7="1X a week"),J25,
IF(AND(B7="Deep",C8=11000,E7="1X a week"),J26,IF(AND(B7="DeepC8=11500,E7="1X a week"),J27,
IF(AND(B7="Deep",C8=12000,E7="1X a week"),J28,IF(AND(B7="Deep",C8=12500,E7="1X a week"),J29,
IF(AND(B7="Deep",C8=13000,E7="1X a week"),J30,IF(AND(B7="Deep",C8=13500,E7="1X a week"),J31,
IF(AND(B7="Deep",C8=14000,E7="1X a week"),J32,IF(AND(B7="Deep",C8=14500,E7="1X a week"),J33,
IF(AND(B7="Deep",C8=15000,E7="1X a week"),J33,IF(AND(B7="Deep",C8=15500,E7="1X a week"),J34,
IF(AND(B7="Deep",C8=15500,E7="1X a week"),J35,IF(AND(B7="Deep",C8=16000,E7="1X a week"),J36,
IF(AND(B7="Deep",C8=16500,E7="1X a week"),J37,IF(AND(B7="Deep",C8=17000,E7="1X a week"),J38,
IF(AND(B7="Deep",C8=17500,E7="1X a week"),J39,IF(AND(B7="Deep",C8=18000,E7="1X a week"),I40,
IF(AND(B7="Deep",C8=18500,E7="1X a week"),J41,IF(AND(B7="Deep",C8=19000,E7="1X a week"),J42,
IF(AND(B7="Deep",C8=19500,E7="1X a week"),J43,IF(AND(B7="Deep",C8=20000,E7="1X a week"),J44,
IF(AND(B7="Complete",C8=2000,E7="1X a week"),K9,IF(AND(B7=" Complete ",C8=2500,E7="1X a week"),K10,
IF(AND(B7=" Complete ",C8=3000,E7="1X a week"),J11,IF(AND(B7=" Complete ",C8=3500,E7="1X a week"),J12,
IF(AND(B7=" Complete ",C8=4000,E7="1X a week"),K13,IF(AND(B7=" Complete ",C8=4500,E7="1X a week"),K14,
IF(AND(B7=" Complete ",C8=5000,E7="1X a week"),K15,IF(AND(B7=" Complete ",C8=5500,E7="1X a week"),K16,
IF(AND(B7=" Complete ",C8=6000,E7="1X a week"),K17,IF(AND(B7=" Complete ",C8=6500,E7="1X a week"),K18,
IF(AND(B7=" Complete ",C8=7000,E7="1X a week"),K19,IF(AND(B7=" Complete ",C8=7500,E7="1X a week"),K20,
IF(AND(B7=" Complete ",C8=8000,E7="1X a week"),K21,IF(AND(B7=" Complete ",C8=8500,E7="1X a week"),K21,
IF(AND(B7=" Complete ",C8=9000,E7="1X a week"),K22,IF(AND(B7=" Complete ",C8=9500,E7="1X a week"),K23,
IF(AND(B7=" Complete ",C8=10000,E7="1X a week"),K24,IF(AND(B7=" Complete ",C8=10500,E7="1X a week"),K25,
IF(AND(B7=" Complete ",C8=11000,E7="1X a week"),K26,IF(AND(B7=" Complete ",C8=11500,E7="1X a week"),K27,
IF(AND(B7=" Complete ",C8=12000,E7="1X a week"),K28,IF(AND(B7=" Complete ",C8=12500,E7="1X a week"),K29,
IF(AND(B7=" Complete ",C8=13000,E7="1X a week"),K30,IF(AND(B7=" Complete ",C8=13500,E7="1X a week"),K31,
IF(AND(B7=" Complete ",C8=14000,E7="1X a week"),K32,IF(AND(B7=" Complete ",C8=14500,E7="1X a week"),K33,
IF(AND(B7=" Complete ",C8=15000,E7="1X a week"),K33,IF(AND(B7=" Complete ",C8=15500,E7="1X a week"),K34,
IF(AND(B7=" Complete ",C8=15500,E7="1X a week"),K35,IF(AND(B7=" Complete ",C8=16000,E7="1X a week"),K36,
IF(AND(B7=" Complete ",C8=16500,E7="1X a week"),K37,IF(AND(B7=" Complete ",C8=17000,E7="1X a week"),K38,
IF(AND(B7=" Complete ",C8=17500,E7="1X a week"),K39,IF(AND(B7=" Complete ",C8=18000,E7="1X a week"),K40,
IF(AND(B7=" Complete ",C8=18500,E7="1X a week"),K41,IF(AND(B7=" Complete ",C8=19000,E7="1X a week"),K42,
IF(AND(B7=" Complete ",C8=19500,E7="1X a week"),K43,IF(AND(B7=" Complete ",C8=20000,E7="1X a week"),K44""))))))))))))))))))))))))))))))))))))))))))))))))