Can Nested if be applicable to filter dynamically changing range in excel

Issue

Consider a data table in excel with 6 columns Min, Max, x, 17,18,19 and 3 user input. Output expected is an appropriate cell value to be returned from the table
when

  1. my first userinput value lies within Min and Max(from table)
  2. 2nd user input value matching any of the cell in column x
  3. 3rd user input matching either 17/18 or 19

For Eg
Sample Data:

Min Max x    17 18 19
-------------------
1   7   0.5  1  2  3/n
8   10   1   2  5  7/n
8   10   2   8  4  9/n
8   10   3   0  7  4/n
11  12  0.5  3  2  1/n

If my user input is (8.4,2,18)
output expected is 4

Sample data

Solution

=INDEX(D2:F6,SUMPRODUCT((A2:A6<=A9)*(B2:B6>=A9)*(C2:C6=A10)*ROW(A2:A6))-ROW(A1),MATCH(A11,D1:F1,0))

Where SUMPRODUCT calculates the row number meeting conditions for min, max and x minus the row number of the header row for correct indexing.
Match calculates the column for indexing.

enter image description here

Answered By – P.b

Answer Checked By – Gilberto Lyons (AngularFixing Admin)

Leave a Reply

Your email address will not be published.