I am trying to get the largest value in a column that is also higher than a value available in another column and lower than a value available in another column.
The problem is that I don't know which are the values of reference in the other columns
For example, if you look at the attachment, the result I need to get is the value in column A with highest qty in column B but whose price be higher than corresponding value in column C and lower than corresponding value in column D
In the example, the result will be 140, because it is true that highest qty in column B is 70, but the corresponding Act Price (890) is higher than the corresponding Max Price in column D (220)
If then I would change for example the qty in cell B3 to 70, the result of the formula would be 130.
I tried with the following formula but I don't how to tell to excel to find the correct row in the range (if I change the qty to 70 in cell B3 the result is still 140)
how can I fix it?=INDEX($A$2:$A$7; LARGE(IF(($A$2:$A$7>$C$3:$C$8)*($A$2:$A$7<$D$2:$D$7); ROW($B$2:$B$7)-2; ""); 1))
Thank you
Bookmarks