Forum Discussion
Confuse about formula
The formula checks whether B27 is positive AND B20 is less than or equal to W2 AND the sum of B21 and B20 is greater than or equal to W1.
If not, the formula returns the empty string "".
If yes, the formula tries to look up the value of A27 in R7:R15 and the value of C26 in S5:V5.
If both are found, the formula returns the value in S7:V15 in the corresponding row and column.
If at least one of those two is not found, IFERROR causes the formula to return "-".
I like your use of a visual for the INDEX/MATCH aspect of the mystery formula.
But, Hans!, you missed the part where you're to take whatever value that INDEX/MATCH formula retrieves and multiply it by $B27.
=IFERROR(IF(AND($B27>0,$B$20<=W$2,$B$21+$B$20>=W$1),$B27*(INDEX($S$7:$V$15,MATCH($A27,$R$7:$R$15,0),MATCH(C$26,$S$5:$V$5,0))),""),"-")
Between the two of us, though, I think we did alright.
I'd still like to know what's actually IN those columns; what kind of information is being manipulate here? Currency, Inventory, .... ?
Thanks, John. I concentrated so hard on trying to explain the INDEX/MATCH combination that I forgot the multiplication.
