INDEX MATCH not producing expected result

[slam]

Hi lovelies,

I'm actually impressed that I got as far as I have, but something isn't quite right, and I'm not sure what it is.

• On a worksheet called Report, I have a list of Teams in column B and a list of Names in column D.
• On a worksheet called Planning, I have the same column structure of a list of Teams in column B and a list of Names in column D.
• On the Report worksheet, I am trying to pull the values from column H of the planning worksheet where there's a match for both the Team and Name.

This is my array formula:

=INDEX(Planning!H$2:H$393,MATCH(1,($B2=Planning!$B$2:$B$393)*($D2=Planning!$D$2:$D$393),0))

I'm getting results where there isn't a match. What am I doing wrong?

Thanks!

EDIT: Ignore for now, think I found the problem, and its with my own data (as usual!)

 

[slam]

Just in case anyone is reading, I'm now stuck with something even more simple (I think). I just want to modify the formula to display a blank if the result is 0. Struggling with how to wrap it in, do I need an OR with the IFERROR?

=IFERROR(INDEX(Planning!H$2:H$393,MATCH(1,($B2=Planning!$B$2:$B$393)*($D2=Planning!$E$2:$E$393),0)),"")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what sort of values do you have in col H text or numbers?

 

[slam]

Hi Fluff,

Thanks for the response. I updated my profile, and signature too.

The values in column H are numbers with two decimal places. Like I said in my edit, I think I've got the INDEX/MATCH working, and I spot checked some data, though does text vs numbers make a difference?

Could I trouble you to help with my other piece in my post #2?

Thank you so much,
-PJ-

 

[Fluff]

does text vs numbers make a difference?

It does for this to work.

=IFERROR(1/(1/INDEX(Planning!H$2:H$393,MATCH(1,($B2=Planning!$B$2:$B$393)*($D2=Planning!$E$2:$E$393),0))),"")

 

[slam]

Perfect, thank you! I also just realized I can set a custom format like this to hide 0's

0;-0;;@

 

[Fluff]

You're welcome & thanks for the feedback.