range checking & flags

Hi,

I'm trying to leverage a formula to check if the employee ID is listed on another sheet.

Sheet 1 (sanity check):

This is where I want the formula to live. On sheet 1, if the employee ID on the row is not listed anywhere on Sheet 2's Employee ID column (range), then turn the flag on. If the employee ID on the row is listed within the same range, then leave the flag off.

Having the flag turn on when there is not a match/reference will indicate that further investigation needs to happen to confirm that the employee ID should truly not be listed on Sheet 2.

Help?

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Matt_A

You could use an Index/Match with error check. Depending on your data, you may have to change the check of the IF statement depending on your data type. But something like =Iferror(if(Index({Sheet 2 ID},Match([Sheet 1 ID]@row],{Sheet 2 ID}))>0,0,1),1)

This would be a column formula in a check box field.

DKazatsky2

Hi @Martha Hemingway,

Give this formula a try.

=IF(COUNTIF({Employee ID}, @cell = [Employee ID]@row ) > 0, 0, 1)

Hope this helps,

Dave

Martha Hemingway

How would i distinguish between the employee IDs between the two sheets?

Martha Hemingway

I dont think it worked…

Sheet 2: Layoff list

Sheet 1: Suspension list

I need each row on Sheet 1 to flag if the employee ID on sheet 1 is not listed anywhere on Sheet 2. On this screenshot, those top 3 ID's are NOT listed on the Layoff List so I want all three flags to be t

Matt_A

You are looking for a match from sheet 1 to sheet 2, then if one is found on sheet 2 that is what the IF statement is evaluating. If not, or depending on field type, it will give an error which produces the flag.

Matt_A

It appears that you Employee IDs are numeric even though they may not be stored that way. You could try:

=Iferror(if(Value(Index({Sheet 2 ID},Match([Sheet 1 ID]@row ],{Sheet 2 ID})))>0,0,1),1)

If they contain letters, then a sightly different approach is needed.

Paul Newcome

I also suggest an IF/COUNTIFS.

=IF(COUNTIFS({Other Sheet Employee ID Column}, @cell = [Employee ID]@row) = 0, 1)

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.