9

I'm trying to find a solution to compare two separate lists (invited - attended) and create a 3rd list (absent).

The function must look at the key in "Attended List (D,E,F)" and compare it with the key in "Invited List (A,B,C)" and then display missing values in "Absent List (G,H,I)".

About the data

  • Key is a 'unique value' assigned to each invited person.
  • The data in "Invited List (A,B,C)" is comprised of QUERY data.
  • The data in "Attended List (D,E,F)" it copy/pasted values in the cells.

Example Of Desired Outcome

Invitded List (A,B,C)      Attended List (D,E,F)       Absent List (G,H,I)
 A      B       C            D      E       F            G      H        I
Key | Fname | Lname       | Key | Fname | Lname       | Key  | Fname | Lname
------------------------------------------------------------------- 
001   Tim     Smith         002  Mike     Jones         001    Tim     Smith
002   Mike    Jones         004  Jenny    Johnson       003    Amy     Wilson
003   Amy     Wilson
004   Jenny   Johnson

Any assistance is greatly appreciated.

  • Why doesn't vlookup do the job for you? – rahi Nov 4 '16 at 0:48
  • @rahi Thank you for looking at this. vlookup might work, can you please give me some direction? – Mr. B Nov 4 '16 at 2:24
2

You could try something like this:

=if(countif(D:D,A2)=0,A2,"")  

then copy across and down to suit.

COUNTIF

  • This works as a partial result as will return empty strings that should be removed to get the desired final result. – Rubén Nov 11 '16 at 17:54
5

Formula

=ArrayFormula(FILTER(A4:C7,ISERROR(match(A4:A7,D4:D5,0))))

Explanation

MATCH returns an error if the invited doesn't appear on the attended list. ISERROR converts errors to TRUE and values to FALSE This result is used as the filtering criteria. The result is, the list of absents:

|   001 |Tim       |Smith
|   003 |Amy       |Wilson

Just add the proper headers above the formula

0

VLOOKUP is another way you can get the answer you want, but I'm not sure if it presents it the way you want.

From the Google Sheets help article, VLOOKUP,

Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

So if you add a Status column on your list of attendees you can run the VLOOKUP on the invited list, asking it to return the Attended status.

Your data could look something like this:

Before VLOOKUP

In cell D2, your VLOOKUP formula would look like this:

=VLOOKUP(A2, E:H, 4, FALSE)

  • A2 is the key
  • E:H is the range (the list of attendees)
  • 4 refers to the column number in the range of the value you want to return
  • FALSE indicates that you're looking for an exact match on the key

When you fill the formula down from cell D2 to D5, you end up with data looking like this:

After VLOOKUP

Where there was a match, the Status value gets pulled over. Where there's no match, you see an #N/A.

  • This works as a partial result as will return errors that could be used to later filter the invited list in order to get the desired final result. – Rubén Nov 11 '16 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.