Forum Discussion
Simplifying cost calculation using array instead of IF statement
Unless I'm mistaken, after spending a bit of time looking at your formulae, I think you could take good advantage of INDEX and MATCH to zero in on the relevant cells in this table
These hyperlinks will point you to a good reference site to learn how INDEX and MATCH work. And again, unless I'm mistaken, you would be able to eliminate the many IFS conditions comparing J4 with the top row to directly zero in on the appropriate column, and then again to identify the appropriate row based on the items down the left of this table.
Given how well you've mastered IFS and XLOOKUP in combination I have no doubt that you'll be able to use INDEX and MATCH equally well.
One other recommendation, assuming you are working in Excel rather than Google Sheets, you could use (on a Mac) a combination of the Option key and "Return" to format your extensive formulas as I've done with the formula in Q12, below; they're much more readable this way.
unfortunately this is a double post. Here is the solution I gave there:
I can't edit that sheet so can't tweak things but what I think you want is something like this:
=SUMPRODUCT(VSTACK(J8/H4,I8/H4,1,1,1,1,1),XLOOKUP(J4,B3:E3,B4:E10,E4:E10,-1))
so the VSTACK will line the AG, AU up with the corresponding rows in the table and the rest of the 1`s will just sum those values. The XLOOKUP will select the corresponding column based on the AG%. I based this the best I could interpret what you did in the equations you have in that sheet. but if not quite right hopefully will at least give you some ideas on how to make a more 'efficient' equation.