How to Compare Two Lists in Google Sheets Step by Step

To compare two lists in Google Sheets, put the first list in column A and the second in column B, then check each value in A against B with COUNTIF. With list items in A2:A and B2:B, enter =IF(A2="","",IF(COUNTIF($B$2:$B,A2)>0,"Found","Missing")) in C2 and fill it down. The result labels each nonblank item in the first list as present or missing from the second. You can also highlight differences, compare in the reverse direction, and account for duplicates or inconsistent text.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
Two lists, ideally with one item per row and a heading in row 1. This guide assumes the first list is in column A and the second is in column B.
Empty columns beside the lists for results or helper data. If your lists are on separate tabs, you can still compare them; the formulas below explain the basic layout first.

Step-by-Step

Arrange the lists. Put the first set of values in A2:A and the second set in B2:B, leaving row 1 for headers such as First list and Second list. Keep each item in a single cell. The lists do not need to be in the same order or have the same number of rows. If the data is on different tabs, you can copy it temporarily into one tab or adjust the formula to refer to the other tab, as explained below.
Add a results heading. Type a label such as Status in C1. The result column will say whether each value from column A also appears somewhere in column B. This is a membership check, so it does not require the matching items to be on the same row.
Enter the comparison formula. With the first list in A2:A and the second list in B2:B, enter this in C2: =IF(A2="","",IF(COUNTIF($B$2:$B,A2)>0,"Found","Missing")). The outer IF leaves the result blank when A2 is blank. COUNTIF searches the second list for the value in A2, and the inner IF returns Found when there is at least one match or Missing when there is not. For example, if A2 contains Mia Chen and that name appears in B2:B, C2 displays Found.
Fill the formula down. Select C2, then use the small blue fill handle at the lower-right corner of the cell and drag it down to cover the rows in your first list. You can also copy C2, select the cells below it, and paste. Check a few results against the source lists before relying on the output. The dollar signs in $B$2:$B keep the search range fixed as the formula is copied, while A2 changes to A3, A4, and so on.
Check the other direction if needed. The first check answers whether each item in A appears in B. It does not show items that are in B but absent from A. To find those, put a heading in D1 and, with the first list in A2:A and second list in B2:B, enter =IF(B2="","",IF(COUNTIF($A$2:$A,B2)>0,"Found","Missing")) in D2. Fill it down alongside the second list. Any Missing result in either direction identifies a value unique to that list.
Highlight missing values instead of adding labels. To color values in A that do not appear in B, select A2:A, choose Format > Conditional formatting, and set Apply to range to A2:A. Under Format rules, choose Custom formula is. With the first list in A2:A and second list in B2:B, use =AND(A2<>"",COUNTIF($B$2:$B,A2)=0). Choose a formatting style and click Done. The AND check prevents blank cells from being highlighted. Repeat for B2:B using a formula that checks A, if you also want to mark items missing in the other direction.
Review duplicates separately. COUNTIF reports whether a value occurs at least once; it does not compare how many times that value occurs in each list. If you need to check frequency, add a separate count. With the first list in A2:A and second list in B2:B, enter =COUNTIF($B$2:$B,A2) in C2 and fill down. A result of 0 means no occurrence in B, 1 means one occurrence, and 2 or more means the item is repeated there. A value appearing once in both lists can still be a duplicate within a list if that list contains it more than once.
Compare values on separate tabs when appropriate. If the second list is on a tab named Inventory, and the first list remains in A2:A on the current tab, put this in C2: =IF(A2="","",IF(COUNTIF(Inventory!$B$2:$B,A2)>0,"Found","Missing")). This assumes the comparison values are in Inventory!B2:B. If a tab name contains spaces, enclose it in single quotation marks before the exclamation mark, as in ‘Stock List’!B2:B. Adjust the tab and column references to match your sheet.

These formulas work in Google Sheets in a web browser. Excel also has COUNTIF and IF, but its conditional-formatting menus and dialog layout differ from Google Sheets. In some spreadsheet locales, formulas use semicolons instead of commas; if Sheets rejects a formula, check the locale settings and separator style for your file.

Common Mistakes

Comparing row by row when order differs. A formula such as A2=B2 only checks the two cells on that row. Use COUNTIF when a value can appear anywhere in the other list.
Checking only one direction. A comparison from A to B finds items in A that are absent from B, but it does not find extra items in B. Run the reverse check as well when you want a full reconciliation.
Ignoring duplicates. A Found result only proves that a match exists. It does not prove that both lists contain the same number of copies. Use a count comparison when repeated entries matter.
Leaving the search range relative. If you omit the dollar signs around the B range, it can shift as you fill the formula down. Lock the range, for example as $B$2:$B, so every row searches the same list.
Assuming visually similar text is identical. Leading or trailing spaces, nonbreaking spaces, different punctuation, and numbers stored as text can make values behave unexpectedly. Inspect the original cells before deciding that the formula is wrong.

Tips

Make a copy of the spreadsheet before deleting or editing items based on the results. Once you have checked the output, you can filter the Status column to show only Missing rows. Select the header and data, then use Data > Create a filter. Use the filter control in the Status header to display Missing, Found, or both. This is often easier than scrolling through a long results column.

For a quick visual check, keep the original values beside the results instead of replacing them. Add a clear heading to each column, freeze the header row with View > Freeze > 1 row, and avoid sorting only one column, which can disconnect results from their source values. If you sort, select the full data range so each row stays together.

COUNTIF comparisons in Google Sheets are not case-sensitive: Mia Chen and MIA CHEN count as a match. If capitalization should be treated as meaningful, use an exact, case-sensitive comparison. With the first value in A2 and the comparison list in B2:B100, enter =SUMPRODUCT(--EXACT(A2,$B$2:$B$100)) in C2. A result of 0 means no exact case-sensitive match; 1 or higher means a match. Use this only when case matters, since it is a count rather than the Found/Missing label.

For lists that should match after removing accidental spaces or ignoring capitalization, create helper columns instead of changing the source data. With the original first list in A2:A and second list in B2:B, enter =LOWER(TRIM(A2)) in C2 and fill down; enter =LOWER(TRIM(B2)) in D2 and fill down. Then compare the cleaned values in C and D. With the cleaned first list in C2:C and cleaned second list in D2:D, enter =IF(C2="","",IF(COUNTIF($D$2:$D,C2)>0,"Found","Missing")) in E2. TRIM removes ordinary extra spaces at the start and end, and LOWER standardizes letter case. Keep the original columns so you can trace any changed result.

Troubleshooting

Every row says Missing. Confirm the formula points to the correct tab and column, and that the search range includes the populated cells. Check for extra spaces, inconsistent punctuation, or values stored in different formats. A date stored as text, for example, may not match a date value even if both look alike. Try the helper-column method above for stray spaces and case differences.

Some apparent matches are missed. Compare the underlying values, not just how they appear on screen. One cell may contain a hidden space or a different apostrophe character. Click the cell and inspect the formula bar, or temporarily use a helper column to standardize the values. If the entries contain literal asterisks or question marks, note that COUNTIF treats those as wildcard characters in its criteria; use the case-sensitive EXACT formula for literal exact matching instead.

Blank rows show a result or get highlighted. Make sure the formula includes the blank-cell check, such as IF(A2="","", ...), and that the conditional-formatting rule includes the AND check. Also verify that the conditional-formatting Apply to range begins on the same row referenced in its custom formula. If the range starts at A2, the formula should refer to A2, not A1.

The formula is rejected or shows a parse error. Check that parentheses and quotation marks are balanced, that you used straight quotation marks in the formula, and that the tab name is spelled correctly. Some locales require semicolons between arguments instead of commas. If you copied a formula from a formatted document, retype the punctuation directly in the Sheets formula bar.

Frequently Asked Questions

Can I compare lists that are in a different order?
Yes. COUNTIF searches the entire comparison range for each value, so the order does not matter. Use a row-by-row equality check only when the positions themselves are meaningful.

How do I find values that appear in both lists?
Use the same status formula and filter for Found. You can also apply conditional formatting with a COUNTIF rule that checks for a count greater than zero. Remember that Found means at least one match, not that the item occurs the same number of times in both lists.

Does the comparison distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive in Google Sheets, so entries that differ only by capitalization normally count as a match. Use EXACT with SUMPRODUCT if capitalization must match exactly.

Can I compare numbers, names, and dates?
Yes, as long as the values have compatible underlying types. A number formatted as currency can match the same numeric value, but a number stored as text may not. Dates can also fail to match when one cell contains date text instead of a real date value. Check the cell format and stored value when results seem inconsistent.

Will this find duplicates within one list?
The Found/Missing formula does not identify repeats inside a single list; it only checks whether each item occurs in the other list. Use COUNTIF on the same list to count repetitions, then review values with a count greater than one.

Can I compare lists with different lengths?
Yes. The lists can have different numbers of rows. Use a search range that covers all expected entries, such as $B$2:$B, and fill the result formula down for every used row in the list you are checking. If your sheet is very large, a bounded range such as $B$2:$B$5000 can make the intended comparison area clearer.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.