What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
The right way to compare two Google Sheets columns depends on what “compare” means: checking the cells on the same row, testing whether each value appears anywhere in the other list, highlighting results, or creating a separate list of missing entries. Use an IF comparison for row-aligned data, COUNTIF for list membership, conditional formatting for visual checks, and FILTER when you need an output list.
The examples below assume row 1 contains headers and data starts in row 2. Replace the example ranges with your actual columns and end rows.
![Google Workspace Guide: Unlock Every Google App – Elevate Efficiency with Exclusive Tips, Time-Savers & Step-by-Step Screenshots for Quick Mastery [II EDITION]](https://m.media-amazon.com/images/I/41CX0AjJhAL._SL160_.jpg)
First decide what “compare” means
Two columns can contain the same information in different arrangements, so a single formula cannot answer every comparison question. A row comparison treats A2 and B2 as a pair. A membership comparison asks whether the value in B2 occurs anywhere in column A, even if it is on another row. A visual comparison changes formatting without creating a result column, while a difference report returns the unmatched values as a new list.
| Goal | Recommended method | Result |
|---|---|---|
| Compare each row with its counterpart | =A2=B2 or an IF wrapper |
Whether the two cells on that row are equal |
| Check whether each B value appears anywhere in A | COUNTIF membership test |
TRUE/FALSE or Found/Missing for each B value |
| See matches or differences in place | Conditional formatting with a custom formula | Cells highlighted according to the rule |
| Build a list of values missing from the other column | FILTER plus a match condition |
A separate list of unmatched entries |
Do not use a membership test when position matters, and do not assume that a matching name identifies one unique person. Repeated names can produce a valid match while still being ambiguous; use a unique identifier or a combined key when identity matters.
#1 Best Overall
- hole punched
- high quality card stock
- 4 pages
- made in USA
- keyboard shortcuts
Prepare the columns before comparing
Put headers in row 1 and begin the data in row 2 so the formulas do not compare a header with a data value. Decide whether the columns should have the same length, whether blank rows are meaningful, and whether repeated values should count once or be reported repeatedly.
- Check data type: A number stored as text may not behave like a numeric value. Dates can also be stored as text in one column and as date serial numbers in another.
- Check spacing: Leading or trailing spaces make text values different. If the source data is inconsistent, consider helper columns using
TRIMbefore comparing. - Choose the direction: “Values in B that are absent from A” is different from “values in A that are absent from B.”
- Protect the source: Try a formula in an empty column or a copy of the sheet before deleting or normalizing data.
Compare corresponding rows
Use this method when row order is meaningful—for example, when row 2 in both columns belongs to the same transaction, date, or record.
Return Match or Different
- Click an empty result cell, such as C2.
- Enter
=IF(A2=B2,"Match","Different"). - Press Enter, then fill the formula down the result column by dragging the fill handle or copying and pasting it.
The formula compares only A2 with B2, then A3 with B3, and so on. It does not search elsewhere in either column. A TRUE/FALSE result is also sufficient if words are unnecessary: =A2=B2.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Keep two blank cells blank
If empty pairs should not be labeled “Match,” use:
=IF(AND(A2="",B2=""),"",IF(A2=B2,"Match","Different"))
This leaves the result empty only when both cells are blank. A blank on one side and a value on the other is reported as Different.
Highlight row differences instead of adding a result column
Select the range you want to format, such as A2:B, then open Format > Conditional formatting. Choose Custom formula is and enter:
=$A2<>$B2
Choose a style and click Done. The row number in the formula must match the first row of the selected range. To avoid highlighting empty rows, use:
=AND($A2<>"",$A2<>$B2)
That version treats column A as the required populated field. If column B is the required field instead, change the first condition accordingly.
Check whether each value appears anywhere in the other column
Use a membership comparison when the lists may be in different orders. In C2, enter:
=COUNTIF($A$2:$A,B2)>0
Fill the formula down. TRUE means the value in B2 occurs at least once in column A; FALSE means no occurrence was counted. The dollar signs anchor the lookup range while the relative B2 reference changes for each row.
Free tools Windows power users keep installed
One-click scans. No signup required.
Google describes COUNTIF as returning a conditional count across a data column. See the COUNTIF function documentation for syntax and criteria behavior.
Display words instead of TRUE and FALSE
For a report that is easier to read, use:
=IF(COUNTIF($A$2:$A,B2)>0,"Found","Missing")
To test the opposite direction, place a formula beside column A and look in column B:
=IF(COUNTIF($B$2:$B,A2)>0,"Found","Missing")
These formulas answer whether at least one occurrence exists. They do not count how many times a value appears. To show the count itself, use =COUNTIF($A$2:$A,B2).
Rank #2
- Mastering Google Sheets: A Step by Step Handbook for Beginners to Simplify Data Analysis, Boost Productivity, and Unlock Your Full Spreadsheet Potential
- ABIS BOOK
Handle blank cells deliberately
A blank criterion can match blank cells in the lookup range. If you do not want empty B rows marked as Found merely because A contains empty rows, add a blank check:
=IF(B2="","",IF(COUNTIF($A$2:$A,B2)>0,"Found","Missing"))
For a bounded dataset, replacing open-ended ranges such as $A$2:$A with a specific end row, such as $A$2:$A$500, can make the intended comparison easier to audit.
Understand wildcard criteria
COUNTIF criteria support pattern behavior documented by Google, including wildcard characters. If your text can contain wildcard characters that must be treated literally, verify the criteria behavior for that dataset rather than assuming every character is matched as plain text. An unusual literal-matching requirement may call for a different formula design and a small test sample.
Highlight matches with conditional formatting
Conditional formatting is useful when you want to inspect the original list without adding a permanent result column. To highlight values in B that occur anywhere in A:
- Select the data range in column B, for example
B2:BorB2:B500. - Open Format > Conditional formatting.
- In the rule panel, choose Custom formula is.
- Enter
=COUNTIF($A$2:$A,B2)>0. - Choose the fill or text style, then click Done.
The formula is evaluated relative to the first cell in the selected range. Keep $A$2:$A anchored so every B cell checks the same lookup range, while leaving B2 relative so each row checks its own value. Google documents custom formulas, duplicate highlighting, and absolute versus relative references in Use conditional formatting rules in Google Sheets.
Highlight values in A that are also in B
Select column A’s data range and use:
=COUNTIF($B$2:$B,A2)>0
This reverses the lookup direction. If you want to highlight only missing values, negate the test:
=COUNTIF($B$2:$B,A2)=0
Add a nonblank condition when blank rows should remain unformatted, for example:
=AND(A2<>"",COUNTIF($B$2:$B,A2)=0)
Compare columns on different sheets
Conditional-formatting rules have additional cross-sheet reference restrictions. Google’s guidance notes that INDIRECT may be needed when a custom formula refers to another sheet. If the rule does not accept a direct cross-sheet range, create a helper result column with a regular formula first, or adapt the rule with INDIRECT and test it on a small range before applying it broadly.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteGenerate a list of entries missing from the other column
Use FILTER when highlighting is not enough and you need a separate, copyable list. To return values in A that do not occur in B, click an empty output cell and enter:
=FILTER(A2:A,COUNTIF(B2:B,A2:A)=0)
The result spills downward automatically. Each returned item is an entry from A for which the count in B is zero. To find values in B that are absent from A, reverse the ranges:
=FILTER(B2:B,COUNTIF(A2:A,B2:B)=0)
Control the output range
Open-ended ranges are convenient for growing sheets, but bounded ranges make a report’s scope explicit. For rows 2 through 500, use:
=FILTER(A2:A500,COUNTIF(B2:B500,A2:A500)=0)
Keep the source and criteria ranges the same height. Mismatched dimensions can produce an error or an incomplete result.
Exclude blank source rows
If column A contains empty rows, add a nonblank condition:
=FILTER(A2:A, (A2:A<>"")*(COUNTIF(B2:B,A2:A)=0))
The multiplication combines the two TRUE/FALSE conditions: the source cell must be nonblank and absent from B. If your locale uses semicolons instead of commas, enter the separators required by your Google Sheets locale.
Decide how duplicates should appear
FILTER returns rows, so repeated source values can appear repeatedly. If the question is “which distinct values are missing,” wrap the result in UNIQUE:
=UNIQUE(FILTER(A2:A,(A2:A<>"")*(COUNTIF(B2:B,A2:A)=0)))
Recommended Free Tools
Do not remove duplicates when each row represents a separate record and the number of missing rows matters. A repeated name may represent different people; use an employee ID, customer number, or a combined key for reliable identity matching.
Compare both directions for a complete difference report
A one-way check can hide an important imbalance. For example, every value in B may be present in A while A still contains additional entries that B lacks. Run a missing-from-A report and a missing-from-B report separately when you need a complete reconciliation:
=FILTER(A2:A,COUNTIF(B2:B,A2:A)=0)lists entries in A absent from B.=FILTER(B2:B,COUNTIF(A2:A,B2:B)=0)lists entries in B absent from A.
Place the two outputs in separate, empty areas and label each result with its direction. Keeping both lists visible makes it clear whether the discrepancy is an addition on one side, an omission on the other, or both.
Compare duplicates and frequency, not just presence
A membership check returns TRUE when one or more matches exist. It cannot tell you whether both lists contain the same number of occurrences. For example, a name appearing three times in A and once in B still passes a simple membership test.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsUse =COUNTIF($A$2:$A,B2) beside B to see how many times each B value occurs in A. Compare that count with a corresponding count in B when duplicate frequency matters. For a full record comparison, combine stable fields into a helper key, such as an order ID plus date, rather than matching a nonunique description.
Google’s conditional-formatting documentation also shows a duplicate-value rule such as =COUNTIF($A$1:$A$100,A1)>1. Adapt the first-row reference to your selected range and decide whether duplicates should be highlighted in the source list or only reported in a separate column.
Use helper columns for an auditable comparison
A temporary helper area can make a review easier than a single complicated rule. One column can hold a cleaned value, another can hold the COUNTIF result, and a third can hold a readable status such as Found, Missing, or Different. Because each step is visible, you can inspect the input that produced an unexpected result and retain the formulas as an audit trail.
For example, if column A contains the source list and column B contains the list to check, put a membership formula in C, then filter or sort by the status. Keep the original columns unchanged until the exceptions have been reviewed. If the final task is a cleanup, copy the confirmed output to a separate area before removing or replacing any source rows.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Common errors and fixes
Every row says Different
- Confirm that the formula references the intended columns and that the first data row is really row 2.
- Look for spaces, different capitalization, or numbers stored as text.
- Check whether one column contains dates or times with different underlying values.
Values that look identical are Missing
- Inspect for leading or trailing spaces and nonprinting characters.
- Compare the cell types; a text string such as
00123is not the same representation as numeric 123 in every context. - Test a cleaned helper column with functions such as
TRIMbefore changing the original data.
Conditional formatting colors the wrong rows
- Make sure the custom formula’s row number matches the first row of the selected range.
- Use dollar signs only where references should stay fixed.
$A$2:$Ashould remain anchored, whileB2should move down the rows. - Check the order of rules in the conditional-formatting panel if multiple rules apply to the same cells.
FILTER returns an error or no visible result
- Verify that the source and criteria arrays cover matching row counts.
- Check for an existing value in the spill area; a blocked spill range prevents the result from expanding.
- Confirm that no-result behavior is acceptable for your sheet and that blanks are not being treated as matches accidentally.
A cross-sheet rule is rejected
Use a helper column with COUNTIF or follow Google’s cross-sheet conditional-formatting guidance regarding INDIRECT. Test the reference on a small range first, because a syntactically valid rule can still point at the wrong sheet or starting row.
A practical workflow for choosing and applying a method
- State the question precisely: same-row equality, membership anywhere, visual inspection, or a missing-value report.
- Confirm the data boundary: identify the header row, first data row, last relevant row, and whether blanks are valid records.
- Choose the direction: decide whether A is being checked against B, B against A, or both.
- Run a small test: include at least one expected match, one expected difference, one blank, and—if relevant—a duplicate.
- Apply the formula or rule: anchor lookup ranges with dollar signs and keep the first-row reference aligned with the selected range.
- Inspect anomalies: investigate spaces, data types, dates, and duplicate identities before treating the output as a definitive data-cleaning decision.
- Preserve an audit trail: keep the result column or FILTER output until the comparison has been reviewed, especially before deleting rows.
Which method should you use?
Choose row comparison when the two columns are paired records and order is significant. Choose COUNTIF when order is irrelevant and you need a per-row membership status. Choose conditional formatting when the source cells should remain in place and a visual signal is enough. Choose FILTER when another person, process, or export needs an actual list of missing values.
For high-confidence reconciliation, combine methods: use a COUNTIF result to mark membership, a FILTER formula to produce the exception list, and a duplicate or key check when repeated values could represent different records.
Rank #4
![The Google Workspace Bible: [14 in 1] The Ultimate All-in-One Guide from Beginner to Advanced | Including Gmail, Drive, Docs, Sheets, and Every Other App from the Suite](https://m.media-amazon.com/images/I/41AWmhmGFuL._SL160_.jpg)
- The Google Workspace Bible: [14 in 1] The Ultimate All in One Guide from Beginner to Advanced Including Gmail, Drive, Docs, Sheets, and Every Other App from the Suite
- ABIS BOOK
Frequently asked questions
How do I compare two columns row by row?
Enter =IF(A2=B2,"Match","Different") in a result column and fill it down. It compares only cells on the same row. Use =A2=B2 if TRUE or FALSE is sufficient, or add the blank-pair version when two empty cells should stay empty.
How can I check whether values in B appear anywhere in A?
Use =COUNTIF($A$2:$A,B2)>0 beside B and fill down. TRUE means at least one occurrence of B2 is in column A, regardless of row position. Wrap it in IF to display Found or Missing.
How do I find values in A that are not in B?
Use =FILTER(A2:A,COUNTIF(B2:B,A2:A)=0) in an empty area. Reverse A and B to find values in B that are absent from A. Add a nonblank condition if empty source rows should be excluded.
Why does an empty value show as Found?
A blank criterion can match blank cells in the lookup range. Prevent that result with =IF(B2="","",IF(COUNTIF($A$2:$A,B2)>0,"Found","Missing")), or exclude blanks in a FILTER condition.
How do I highlight matching values without adding a formula column?
Select the data range, choose Format > Conditional formatting, select Custom formula is, and enter =COUNTIF($A$2:$A,B2)>0 when formatting B. Keep the lookup range absolute and the row reference relative.
Can I compare columns on different sheets?
Regular helper formulas can reference another sheet. Conditional-formatting rules have extra cross-sheet restrictions; Google notes that INDIRECT may be needed. If a direct reference is rejected, test a helper column or an INDIRECT-based rule on a small range.
How do duplicates affect a comparison?
A membership check only proves that one or more matches exist. It does not prove equal frequency or unique identity. Use COUNTIF to inspect occurrence counts, UNIQUE when a missing-value report should list distinct values, and a unique ID or combined key when names can repeat.
What if values look the same but do not match?
Check for leading or trailing spaces, nonprinting characters, different data types, and dates stored in different forms. Try a helper column with TRIM and compare the cleaned values before changing the originals.
Official references
- Google: Use conditional formatting rules in Google Sheets
- Google Docs Editors Help: COUNTIF
- Google Docs Editors Community: How can I compare values in two columns?
- Google Docs Editors Community: Compare columns in Sheets
Frequently Asked Questions
How do I compare two columns row by row?
Enter =IF(A2=B2,"Match","Different") in a result column and fill down. It compares only cells on the same row.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →How can I check whether values in B appear anywhere in A?
Use =COUNTIF($A$2:$A,B2)>0 beside B. TRUE means B2 appears at least once in column A, regardless of row.
How do I list values missing from the other column?
Use =FILTER(A2:A,COUNTIF(B2:B,A2:A)=0); reverse the ranges to check the other direction.
Why is an empty value marked Found?
Blank criteria can match blank cells. Add IF(B2="","",...) around the membership test to leave empty rows blank.
How do duplicates affect matching?
COUNTIF membership confirms presence, not equal frequency or unique identity. Check counts and use a unique ID or combined key when names repeat.
Recommended Free Tools
The Bottom Line
Use IF for same-row equality, COUNTIF for membership anywhere in the other column, conditional formatting for visual review, and FILTER for a separate missing-value list. Decide how blanks, duplicates, data types, and direction should be handled before acting on the results.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
