Issue with long nested IF formula (alternative way?)

[Luca_Morrone91]

Hi,

I seem to have an issue with a long IF formula I am trying to build. The formula itself works, but I think there could be a quicker and more clever way to achieve the same outcome. I'd like your help.

Context:

• A department can place 2 orders for product A: Order #1, and Order #2.
• Depending on their order quantity, the quantity of the components A1, A2, A3, etc. will be delivered.
• The sum of Order 1 and 2 gives the total (cumulative) order quantity.
• The IF function comes into play because, depending on the 1st and 2nd order quantity, the quantity of components dispatched will need to vary (the cumulative figure is very important because dictates how many pieces the department should receive for each component).

See file attached. I have created long and manual IF functions in CELLS B16-B20 (B21 is separate), but I would like to understand if there is a better way to do that. Besides, my formulas in CELLS B16-B20 does not take into account all possible scenarios because it'd be way too long. I am sure there should be another way but I can't think of it... I tried using IFS to make it shorter, but I don't have that function on MS Excel 2016.

The inputs are on B9 an B11. And the outputs are in B13, B16-B20, B21.

Any suggestion/help? I am tagging you again @Riny_van_Eekelen just in case you may be able to kindly help here.

Thank you very much,

Luca

[#2 · Accepted solution]

@Luca_Morrone91 This is typically something that you can handle with INDEX and MATCH. See attached. Have used some named ranges to avoid long/and absolute references in the formula. And by the way, I changed the headers on row 1 to numbers 1 through 26 in stead of 1st, 2nd, 3rd etc.

=INDEX(Quantities,MATCH(A16,ProdDescr,0),MATCH($B$13,NbrOfOrders,0))-INDEX(Quantities,MATCH(A16,ProdDescr,0),MATCH($B$9,NbrOfOrders,0))

[#3]

wow, thanks Riny, that was quick. Truly appreciate your help :)