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
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 183 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!