FlummoxedByExcel
New Member
 Joined
 Jun 1, 2021
 Messages
 15
 Office Version

 365
 Platform

 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),
)))))))
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),
)))))))