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

FlummoxedByExcel

New Member
Joined
Jun 1, 2021
Messages
15
Office Version
  1. 365
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,259
Office Version
  1. 365
Platform
  1. Windows
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),
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,755
Office Version
  1. 2010
Platform
  1. Windows
It would help if you put your data using the XL2BB minisheet tool, example:

varios 11oct2021.xlsm
ABCDEFGHIJKLMNOPQ
1DistributorRate4.5L20L12L9L8L7.5L7L6.75L6L5L4L3L2.25L2L1.5L
2Company Name20.8310.9226.8823.3320.8319.0217.8616.716.1214.3912.079.767.445.715.133.97
Rules_NY


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

varios 11oct2021.xlsm
ACEIJ
1
2TEI PO#DistributorPack SzRate
3PO68559Company Name9L18.35
4PO68560Company Name9L20.83
Sheet1
Cell Formulas
RangeFormula
J3:J4J3=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.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,259
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1Rate4.5L20L12L8L
2Cust 17371493985
3Cust 2487114563
4Cust 34550527468
Rules_NY


FlummoxedByExcel.xlsm
ABCDEF
1Rate4.5L20L12L8L
2Cust 1178633964
3Cust 2446523875
4Cust 36430915294
Rules_N19Y


FlummoxedByExcel.xlsm
CEIJ
4PO68559Cust 220L2
5PO68560Cust 28L75
6PO68560Cust 2retail44
7PO11111Cust 34.5L30
Sheet1
Cell Formulas
RangeFormula
J4:J7J4=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

New Member
Joined
Jun 1, 2021
Messages
15
Office Version
  1. 365
Platform
  1. MacOS
@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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,259
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
14,755
Office Version
  1. 2010
Platform
  1. Windows
@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?"
 
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,163,407
Messages
5,831,436
Members
430,070
Latest member
Renske

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