Index Match with 4 column match.

[Jing]

Hey Guys,

So i have the following formula, it is basically matching 4 Criteria in four different columns then pulling the result from the 24th column of the index. however due to the nature of the formula it is very slow

{=INDEX(Data!$A$1:$Y$6506,MATCH(Summary!B13&Summary!C13&Summary!D13&Summary!E13,Data!$A:$A&Data!$B:$B&Data!$L:$L&Data!$F:$F,0),24)}

Is there another way to write this type of a lookup formula without it being very slow?

Thank you

 

[Jonmo1]

I would do the concatenation on the Data Sheet.

On the Data Sheet, say column M (or any available column)
M2: =A2&B2&L2&F2
Filled down to end of data

Then use
=INDEX(Data!$X$1:$X$6506,MATCH(Summary!B13&Summary!C13&Summary!D13&Summary!E13,Data!$M:$M,0))

 

[Jing]

WHOA!! that is way faster... awesome thank you so much

 

[Jonmo1]

Glad to help, thanks for the feedback.