Formula Help - Cross Reference Sheet

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.

Reference Sheet

Names do not include "Term-"

Smartsheet Formula Inquiry - TERM (Membership Directory).png

.
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!! 😀

Find more posts tagged with

Formulas

Accepted answers

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!

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.