Forum Discussion

Maran66's avatar
Maran66
Copper Contributor
May 15, 2021

Combining Index and Match Functions

I have achieved retrieving data from column L & M to column D & E by using formula " {=INDEX($L$3:$L$8,MATCH(B3&C3,$I$3:$I$8&$K$3:$K$8,0))} " in office 365 excel version. Whereas, not getting the result by using same formula when checking from another machine / excel 2013 version. Please find the attachment for reference. Please suggest on this how to get the results in all the machines / all the excel version. 

Formulas and Functions
office 365

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 15, 2021

    Maran66 

    I don't see why this wouldn't work in older versions, but try this as alternative:

     

    =INDEX($L$3:$L$8,MATCH(1,INDEX(($I$3:$I$8=B3)*($K$3:$K$8=C3),,),0))

     

    This is an 'ordinary' formula, you do not have to confirm it with Ctrl+Shift+Enter.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 15, 2021

    Maran66 

    It works as regular formula (not array one) on both versions. And what's wrong on 2013? Screenshot is practically not recognizable.