Hi Reader,

Welcome to the Google Sheets Tips newsletter #290, your Monday morning espresso, in spreadsheet form!

A big thank you to this week's sponsors who help keep this newsletter free to read:

DOKIN Sync all your system data into Sheets, Slides, & Docs Sync marketing, sales, and finance data into your Sheets. With Dokin, you can automatically sync data from 20+ apps into Google Workspace. Keep spreadsheets, documents, and presentations up to date with data from HubSpot, GA4, LinkedIn Ads, Salesforce, and more. Or use the writeback feature to edit system records directly from Sheets. Try it for free today!

➜ New videos on YouTube

Check out my newest video tutorials on YouTube:

🎥 How to use the powerful MAP Function in Google Sheets​

🎥 How To Add Superscript And Subscript Characters in Google Sheets​

_______

➜ News

I.
​The Explore feature in Google Sheets is disappearing at the end of the month. Not surprising since it was an old feature. I'm sure it's making way for a more powerful, native AI tool in Sheets in the future.

​Read more here >>​

II.
Bard — Google's AI chat app — is now Gemini. This unifies Google's AI efforts around their new Gemini models, which are claimed to close the gap to ChatGPT's best models.

​Google AI updates >>​

_______

➜ Google Sheets Tip #290: Spot the Difference (How to Compare Lists)

In this tip, we look at a classic problem that everyone who works with data will face sooner or later: comparing 2 lists to find the differences.

For example, suppose we have these two lists of names in our Sheet that we want to compare:

Let's find out which names are in List 1 but not in List 2, and vice versa.

As with most spreadsheet problems, there are lots of ways you can do this.

Ten years ago, my go-to method for this was to use VLOOKUP to search for each name in the other list. (And if I was doing this today, I'd use an XLOOKUP instead.)

But today, I'll show you an alternative method using the COUNTIF function.

So we'll start by counting how many times each name in column A (List 1) appears in column E (List 2). Add this formula in cell B2 and drag it down to the bottom of your list:

This formula returns 0 if the name is missing. So let's add that as an equality test:

This gives a TRUE for missing names and FALSE otherwise. We could stop here and just look for the rows with TRUE to see the names that weren't in List 2.

But since we have a TRUE/FALSE we can wrap it with an IF function to make the output a little more user-friendly:

For names not in List 2, the formula outputs "Not in List 2". For everything else, the row is left blank.

In the image above, we can see that "Michael Scott" was not found in List 2.

To generate a list of all the names missing from List 2, use a quick FILTER function:

Other way around

Follow the same steps with list 2 to find out which names on list 2 are missing from list 1.

This time, our range is column A (list 1), and our criterion, or search term, is column E (list 2). The first part of the formula is:

From here, follow the same steps previously mentioned to find the missing items.

And there we have it!

What do you think? What's your preferred way to compare lists?

Template

Make a copy of the template here, so you can see these formulas firsthand.

​🔗 Click here to open a view-only copy >>​

Feel free to make a copy: File > Make a copy…

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Railway map of Antarctica​