“Now, let’s imagine we are working with a much larger set of data and that visual inspection of the table is simply not practical.”
We already know what our range is and the row number for 1000 items per box is 4.
We also know that 5 boxes is column number 2 in our array.
Therefore, our formula should be ‘=INDEX(B3:E6,4,2)’
(in our actual example below, the row number and column number are replaced by cell references that contain these values but this is all the same).
Say we wanted to see what our price per box will be if we want to purchase 10 boxes of 500 products per box.
We can simply change our entry into the Qty per Box field (B9) and out Number of Boxes field (B10) to look up the value in the intersection of the row and column for each.
Again, the actual formula contains the cell references that contain the row and column numbers for our look up.
If you replaced the cell references C9 and C10 with the actual number 3 in both, you will get the same result.