I have an excel workbook with 2 sheets. One sheet is called Roster and sheet 2 is called Safety Meeting. On sheet 2(Safety Meeting) is a column with [Id#-name] for those who attended. How do I get “Yes” or “No”return on sheet 1(Roster) for those who attended on sheet 2(Safety Meeting). Also, Roster sheet only has ID# and on Safety Meeting sheet it has ID#andName, but I only need to match the ID number. I was trying IF and MATCH functions, but having a column with ID numbers and name is throwing me off.

CodePudding user response：

So something like:

if(iferror(match(id_num,range_safety_meeting,0),0)>0,"Yes","No")

As you don't show what any of the data looks like I have just shown the structure.

Match has two compulsory arguments, while the third is optional, however I always set 0 for an exact match or 1 for a descending or -1 for an ascending result.

CodePudding user response：

OK, so you have a sheet called Roster, and a sheet called Safety Meeting, and in the second sheet you have a column with ID and name, separated with a hyphen?

Option 1: the Roster sheet has the same names as the Safety Meeting sheet

1. Combine the ID and name on the Roster sheet in the same format as in Safety Meeting:

=A:A  & "-" & B:B        for Excel 2010
=@A:A & "-" & @B:B       for current version
Gives e.g. "24601-Valjean"

2. MATCH this combined ID-name in the Safety Meeting column. I have named that column attendees

=MATCH(  A:A & "-" &  B:B, attendees, 0)    Excel 2010
=XMATCH(@A:A & "-" & @B:B, attendees)       Current
Gives row number if found or #N/A if not found

3. Convert to TRUE/FALSE

=NOT( ISNA( MATCH( A:A & "-" &  B:B, attendees, 0)))      Excel 2010
=NOT( ISNA( MATCH(@A:A & "-" & @B:B, attendees, 0)))      Current

Option 2: the Roster sheet does not have exactly the same names as Safety Meeting

1. Choose a helper column for the Safety Meeting attendees. This could be on the same sheet, or on a hidden sheet. I have named it attendee_id
2. Find the - in attendees:

=FIND("-",  attendees)    Excel 2010
=FIND("-", @attendees)    Current

3. Get the first (that-many minus one) characters from attendees - this is the ID:

=LEFT( attendees, FIND("-",  attendees) - 1 )     Excel 2010
=LEFT(@attendees, FIND("-", @attendees) - 1 )     Current

4. Look up Roster IDs in this helper column:

"At meeting?" column
=NOT( ISNA( MATCH( A:A, attendee_id, 0)))    Excel 2010
=NOT( ISNA( MATCH(@A:A, attendee_id, 0)))    Current

5. Observe this didn't work:

6. The Roster IDs have numeric values, while attendee_id has text value. They must be made to match. Replace A:A with TEXT(A:A,0) to format the number as text.

=NOT( ISNA( MATCH( TEXT( A:A, 0), attendee_id, 0)))   Excel 2010
=NOT( ISNA( MATCH( TEXT(@A:A, 0), attendee_id, 0)))   Current