How to simplify a nested "IF" formula with a test & reference to multiple VLOOKUP tables?

[FlummoxedByExcel]

Hi Excel geniuses, I'm looking for help simplifying this ugly (but working!) formula.
First it performs a test to see if a PO is over or under a certain number. (Thank you @Peter_SSs and @etaf !)
Based on that PO Number, I direct the next calculations to one of two VLOOKUP tables — one has old pricing, one has new pricing.
The VLOOKUP tables contain a list of customers in column A. Cols B - T in the customer name row are prices for different box sizes, which vary by customer.
My worksheet first looks for the box size in Col. I, then the customer name in Column E. It finds an exact match for the customer and the box size in the VLOOKUP table, then pulls the box price from the correct column. I'd like to find a way to avoid all this repetition if possible. Thanks to anyone who can help! I did try "IFS" but it returned an error.
(The formula is actually much longer than this as there are many more box sizes! I cut it down for clarity)
=IF(RIGHT(C3,5)*1>=68560,
IF(I4="9L",VLOOKUP(E4,Rules_NY!A:T,2,FALSE),
IF(I4="retail",VLOOKUP(E4,Rules_NY!A:T,2,FALSE),
IF(I4="4.5L",VLOOKUP(E4,Rules_NY!A:T,3,FALSE),
IF(I4="20L",VLOOKUP(E4,Rules_NY!A:T,4,FALSE),
IF(I4="12L",VLOOKUP(E4,Rules_NY!A:T,5,FALSE),
IF(I4="8L",VLOOKUP(E4,Rules_NY!A:T,6,FALSE),
)))))),
IF(I4="9L",VLOOKUP(E4,Rules_N19Y!A:T,2,FALSE),
IF(I4="retail",VLOOKUP(E4,Rules_N19Y!A:T,2,FALSE),
IF(I4="4.5L",VLOOKUP(E4,Rules_N19Y!A:T,3,FALSE),
IF(I4="20L",VLOOKUP(E4,Rules_N19Y!A:T,4,FALSE),
IF(I4="12L",VLOOKUP(E4,Rules_N19Y!A:T,5,FALSE),
IF(I4="8L",VLOOKUP(E4,Rules_N19Y!A:T,6,FALSE),
)))))))

 

[Peter_SSs]

I don't see the branch in either one of them

I don't see that as a reason not to try at least one of them, which it seems like you have not done as steve the fish's formula works* for me if the final VLOOKUP in his formula is expanded as I explained in post #7

* I am assuming that the formula in post #1 has a typo as it is looking in row 3 to get the PO number but row 4 to get the name and pack size and that does not seem to match the images you have just posted.

=IF(RIGHT(C3,5)*1>=68560,
IF(I4="9L",VLOOKUP(E4,Rules_NY!A:T,2,FALSE),
IF(I4="retail",VLOOKUP(E4,Rules_NY!A:T,2,FALSE),

 

[DanteAmor]

It would help if you put your data using the XL2BB minisheet tool, example:

varios 11oct2021.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Distributor	Rate	4.5L	20L	12L	9L	8L	7.5L	7L	6.75L	6L	5L	4L	3L	2.25L	2L	1.5L
2	Company Name	20.83	10.92	26.88	23.33	20.83	19.02	17.86	16.7	16.12	14.39	12.07	9.76	7.44	5.71	5.13	3.97
Rules_NY

With this new information, you can use the following formula:

varios 11oct2021.xlsm
	A		C		E				I	J
1
2			TEI PO#		Distributor				Pack Sz	Rate
3			PO68559		Company Name				9L	18.35
4			PO68560		Company Name				9L	20.83
Sheet1

Cell Formulas
Range	Formula
J3:J4	J3	=VLOOKUP(E3,IF(RIGHT(C3,5)*1>=68560,Rules_NY!A:T,Rules_N19Y!A:T),MATCH(I3,Rules_NY!$A$1:$Q$1,0),0)

First part: =VLOOKUP(E3,IF(RIGHT(C3,5)*1>=68560,Rules_NY!A:T,Rules_N19Y!A:T)
The formula looks for the distributor that is in cell C3, the formula checks the PO value, if it is> = 68560 then it looks in the first sheet. If it is less, then look on the second sheet.

Second part: MATCH(I3,Rules_NY!$A$1:$Q$1,0),0)
The formula finds the column according to the Pack Sz,
In your example, package 9L is in column F (number 6), See the sample sheet "Rules_NY".
So it is as if the formula would search A:T and get the data from column 6.

 

[Peter_SSs]

Now that you have posted those images, we can see that your headings in the 'Rules' sheets can be used to assist the lookups, though I think a slight variation is required to Dante's latest suggestion to cope with those headings that do not match. See if this works for you.

FlummoxedByExcel.xlsm
	A	B	C	D	E	F
1		Rate	4.5L	20L	12L	8L
2	Cust 1	73	71	49	39	85
3	Cust 2	48	71	14	56	3
4	Cust 3	45	50	52	74	68
Rules_NY

FlummoxedByExcel.xlsm
	A	B	C	D	E	F
1		Rate	4.5L	20L	12L	8L
2	Cust 1	17	86	3	39	64
3	Cust 2	44	65	2	38	75
4	Cust 3	64	30	91	52	94
Rules_N19Y

FlummoxedByExcel.xlsm
	C		E				I	J
4	PO68559		Cust 2				20L	2
5	PO68560		Cust 2				8L	75
6	PO68560		Cust 2				retail	44
7	PO11111		Cust 3				4.5L	30
Sheet1

Cell Formulas
Range	Formula
J4:J7	J4	=VLOOKUP(E4,CHOOSE((RIGHT(C4,5)-68560>0)+1,Rules_N19Y!A:T,Rules_NY!A:T),IFNA(MATCH(I4,Rules_NY!A$1:T$1,0),2),0)

 

[FlummoxedByExcel]

@steve the fish thank you, I marked your first reply as the solution — I had to look at it when I wasn't exhausted, I de-constructed it today and could see the branch this time. I understand why it works although not this section: 0+RIGHT(C3,5)>=68560)+1. I understand this part: RIGHT(C3,5)>=68560) but not the 0+ before and the +1 after. It is a very elegant formula. I will have to consult my Excel book to learn "CHOOSE" function.
@DanteAmor Thank you, we are going to be moving in that direction with next iteration of the pricing table, it will be based on a reference to a pricing level instead of a customer name which will make it MUCH more manageable. For now I am working with a large legacy table & a system that I don't have time to re-create.
@Peter_SSs thanks for reminding me to look again at original solution. I learn so much from this board. Last time I learned the "RIGHT" function and was able to apply it here. This time I learned CHOOSE and Array. Since I don't have to create spreadsheets every day and don't use Excel for data crunching I haven't had to build my skills beyond basic level, but now am facing more difficult challenges. This board is such a gift, thank you to folks who take the time and energy to help and teach. ?

 

[Peter_SSs]

but not the 0+ before and the +1 after.

RIGHT(C3,5) returns a text string. Adding 0 to it forces it to become a number so that it can then be compared to the existing number 68560.

(RIGHT(C4,5)-68560>0) returns FALSE or TRUE result. If forced to numbers, these equate to 0 and 1. However, in the CHOOSE function we need to choose between the 1st option and the 2nd option to decide the lookup range.
Adding 1 to those FALSE or TRUE results changes them from 0 and 1 into 1 and 2 ready for the CHHOSE function.

@Peter_SSs thanks for reminding me to look again at original solution.

You're welcome.
BTW, did you try the formula suggestion from post #13? If I have understood correctly then that function saves you having to create that rather long array in steve's formula suggestion or the lookup table in Dante's first suggestion.

 

[DanteAmor]

@DanteAmor Thank you, we are going to be moving in that direction with next iteration of the pricing table, it will be based on a reference to a pricing level instead of a customer name which will make it MUCH more manageable. For now I am working with a large legacy table & a system that I don't have time to re-create.

In post #12 I don't have a table, it is a formula that is based on the headings of each sheet. You don't actually have to modify anything on your sheets.
If you gave yourself the opportunity to test the solutions presented, you might be able to appreciate simpler solutions.

Because that's exactly what you asked for in the title of your question:
"How to simplify a nested "IF" formula with a test & reference to multiple VLOOKUP tables?"