Excel formula to select data range based on a condition

user_3908926 0
Viewed 582

I am trying to get the value from column A based on conditions below: a) column number will be where the value match for (1101,1138,1554...) e.g: 1101 is column B b) Row number will be where the number 1,2,3.. match in the column that came from condition (a) e.g-B:B enter image description here

formula excel-formula excel
1 Answers

You can use a combination of INDEX-MATCH functions to find the value in column A:A with 2 parameters.

Result Example

Breakdown of Formula:

=INDEX(A3:A7,
      MATCH(L2,
         INDEX(B3:G7,0,MATCH(K2,B2:G2,0)),0
      )
 )
  • The 1st line in the formula is your return range.
  • The 2nd line is the row to match the lookup on.
  • The 3rd line finds the column for the row lookup by using another INDEX-MATCH.

You'll have to modify the formula to suit your needs of course.

user_115907040
Related