Confuse about formula

Arsalan975 

HansVogelaar has given you a literal translation of the formula. I'm going to try to put it into English words, which is a way of thinking that I'd commend to you if you are going to be writing formulas yourself. Spell out in words what you want to do, and only after that convert it to cell references and the like.

The IF part of the formula is essentially checking to make sure

• Unit Count (B27) contains a number greater than 0 AND
• ACO Attachment (B20) is less than or equal to Attachment (W2) AND
• ACO Attachment (B20) plus ACCO Limit is greater than or equal to Upper Limit (W1)

Since all of those are true, all conditions are met, the computation can be executed. If any one of those conditions is not met, then the formula yields a blank. (That's what "" ends up meaning). This formula, the cell references adjusting as you move down, are, I'm sure, in the rest of columns C and D, but because the rest of column B is itself blank, that's why all the other cells in columns C and D are also blank. I'm pretty sure they have variations of this same formula in them, but the first condition there isn't met (column B, rows 28 through 34 are blank). If you entered a number in any of them, a number greater than zero, they too would become populated).

The computational part of the formula says

• Multiply the Unit Count (B27) [which in this case is  the value of 10]
• By the value you find in the table (second image) where PPT and 1 x 1 intersect
  • (HansVogelaar has explained how that intersection is found)
  • [which in this case is 169]
• giving the result of 10 x 169 or 1690

==========================================

Now, very frankly, I don't know the full context here, but it really looks to me as if whoever created that formula and the rest of the spreadsheet was making it more complicated than it needs to be. Two of the conditions specified in the IF statement (those having to do with the ACO attachment and ACO limit seem entirely redundant, in that the values that have been entered into those cells seem designed to be right at the limit. If, in fact, those values on the main active part of the spreadsheet can and do vary in a meaningful way, then by all means leave those conditions there, but given that they seem like extreme outer limits which are being easily met, all they're doing is making the formula unnecessarily long and harder to read.

Arsalan975 I’m on my phone at the moment, with its limited keyboard, so not about to attempt a full explanation. But if you read through the responses you’ve already gotten, replacing the abstract cell references with the content from the various cells, you should be able to figure it out. 
If you haven’t by the time I get to a desktop, I’ll chime in again. 

Arsalan975 

In your screenshots:

B27=10, so B27>0.

B20=1,000,000 and W2=1,000,000 so B20<=W2.

B21=1,000,000, so B21+B20=2,000,000. and W1=2,000,000, so B21+B20>=W1.

Combining these, AND($B27>0,$B$20<=W$2,$B$21+$B$20>=W$1) evaluates to TRUE, so the formulas evaluates $B27*(INDEX($S$7:$V$15,MATCH($A27,$R$7:$R$15,0),MATCH(C$26,$S$5:$V$5,0))).

A27="PPT". This is found in R7, so MATCH($A27,$R$7:$R$15,0)=1.

C26="1 x 1". This is found in S5, so MATCH(C$26,$S$5:$V$5,0)=1.

INDEX($S$7:$V$15,MATCH($A27,$R$7:$R$15,0),MATCH(C$26,$S$5:$V$5,0)) returns the cell in the 1st row and 1st column of S7:V15, i.e. 169.

B27=10, as we already noted, so the product returned is 10*169 = 169.

HansVogelaar 

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, .... ?