Combining Index and Match Functions

Occasional Visitor

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. 

2 Replies

@Maran66 

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

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