Index Match with multiple criteria not using arrays

[benwork]

Hi all

Im using the index match feature with multiple criteria to get a returned value, but its slowed the sheet to a crawl.

Any other ideas to return the text value? Im not great at VB

I should mention its a shared document and I think arrays have issues with it being shared?

{=INDEX('CB'!E:E,MATCH(1,(Summary!C2='CB'!C:C)*(Summary!C1='CB'!B:B)*(Summary!C3='CB'!D:D),0))}

Thanks in advanced

Ben

 

[RasGhul]

Hi Ben,

It's always better to try and add an exception to the formula so that it reduces the rows that it looks at instead of whole columns...

 

[benwork]

Sorry for the very late reply, the project got bumped from the priority list.

Forgive my ignorance, what do you mean by add an exception to the formula, can you pls give an example?

Thanks

Ben

 

[Fluff]

How about
=INDEX('CB'!E:E,MATCH(C2&"|"&C1&"|"&C3,index('CB'!C:C&"|"&'CB'!B:B&"|"&'CB'!D:D,0),0))

Although I would recommend not using whole column references, as that will always slow things down.