Formula Help - Cross Reference Sheet

Marcela Hernandez

Hello,

I need help solving this issue that is stumping me. To goal of the cross sheet reference formula is to highlight which employees are no longer with the company. There is logic in place that when the name contains "TERM-", a column will be checked to help indicate to exclude from reports and counts. However, as shown below, there are rows being struck out for active employees.

1. Reference Sheet

Names do not include "Term-"

.
2. Main sheet which includes the formula below on main sheet.

Sheet name: SOCM Forum Membership Form

Formula: =IFERROR(INDEX({OCM Membership Directory |Inactive}, MATCH(Email@row, {OCM Membership Directory |Email}, 1)), "TERM-")

Thank you in advance for your review and help!! 😀

Janae G.

Hello!

Okay first thing is I would put 0 in your MATCH function to search for an exact match instead of 1, which will tell MATCH to look for the largest value less than or equal to your search value.

=IFERROR(INDEX({OCM Membership Directory |Inactive}, MATCH(Email@row, {OCM Membership Directory |Email}, 0)), "TERM-")

Otherwise, check to make sure that cross sheet references are looking at the range you want, ex. the whole column.

Hope this helps and good luck!

Janae G.

Hello!

Okay first thing is I would put 0 in your MATCH function to search for an exact match instead of 1, which will tell MATCH to look for the largest value less than or equal to your search value.

=IFERROR(INDEX({OCM Membership Directory |Inactive}, MATCH(Email@row, {OCM Membership Directory |Email}, 0)), "TERM-")

Otherwise, check to make sure that cross sheet references are looking at the range you want, ex. the whole column.

Hope this helps and good luck!

Marcela Hernandez

Thank you @Janae G.! I knew it was something minor. I made another tweak to get it to work how I need. Appreciate your review!