INDEX/MATCH Help
Hi all,
I created the following formula to search through another sheet and, if a location has submitted a row in the applicable month, it is supposed to check the box on our confirmation sheet. It worked for January (see below) but is not working for February.
What am I doing wrong?
=IFERROR(IF(INDEX({Month Name}, MATCH([Branch Name]@row, {Branch Name}, 0)) = "February", 1, 0), 0)
This is the reference sheet to show #1113 and #1118 which isn't showing as checked above.
Best Answer

By combining IF with INDEX MATCH you are restricting the results to the first match only.
The INDEX MATCH returns the month in the first row where the branch name matches. You then use an IF to check the box if this month is January and it looks like the formula works.
However, come February, the box will not be ticked even if there is a row with February in it, as the row that is returned by the INDEX MATCH is still the row with January in it.
If you delete the January rows from your sheet you should see February working.
Instead of combining IF and INDEX MACTH, I suggest you use an IF and COUNTIFS.
=IF(COUNTIFS({Branch Name}, [Branch Name]@row, {Month Name}, "March") >= 1, 1, 0)
This formula counts all the rows where the Branch Name matches that in the current row and the Month Name matches the month entered. If this is 1 or more (in other words there is at least one row where that branch name appears with that month name), the IF returns 1 (checks the box). If this is less than 1 it returns 0.
Let me know if that works for you.
Answers

By combining IF with INDEX MATCH you are restricting the results to the first match only.
The INDEX MATCH returns the month in the first row where the branch name matches. You then use an IF to check the box if this month is January and it looks like the formula works.
However, come February, the box will not be ticked even if there is a row with February in it, as the row that is returned by the INDEX MATCH is still the row with January in it.
If you delete the January rows from your sheet you should see February working.
Instead of combining IF and INDEX MACTH, I suggest you use an IF and COUNTIFS.
=IF(COUNTIFS({Branch Name}, [Branch Name]@row, {Month Name}, "March") >= 1, 1, 0)
This formula counts all the rows where the Branch Name matches that in the current row and the Month Name matches the month entered. If this is 1 or more (in other words there is at least one row where that branch name appears with that month name), the IF returns 1 (checks the box). If this is less than 1 it returns 0.
Let me know if that works for you.

Thank you so much @KPH !! It worked :)

Thanks for letting me know. Glad I could help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!