Why is my Multi-criteria Index Match Formula not Working?

[JamesA11]

Hello

I am trying to do a 2 criteria lookup using index match. This is what I thought would work but it just returns #N/A:

{=INDEX(BSPs!$H$2:H$112596,MATCH(1,(A2=BSPs!$D$2:D$112596)*(C2=BSPs!$F$2:F$112596),0))}

It is entered using CSE but just doesn't work. Can anyone see the issue?

Thanks

 

[Fluff]

Looks good to me. Are you sure that you have an exact match to both A2 & C2 on the same row?

 

[jasonb75]

Are you entering the formula, then dragging right?

If you are then the $ symbols need to be consistent, as it is now dragging right will give you 2d arrays where there should be 1d arrays.

{=INDEX(BSPs!$H$2:H$112596,MATCH(1,(A2=BSPs!$D$2:D$112596)*(C2=BSPs!$F$2:F$112596),0))}