Search code examples
excelexcel-formulaformula

Excel formula to select data range based on a condition

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

Solution

  • 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.