INDEX/MATCH Help

ElizabethOwen

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.

KPH

Hi @ElizabethOwen

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.

KPH

Hi @ElizabethOwen

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.

ElizabethOwen

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

KPH

Thanks for letting me know. Glad I could help.