|

AI Product Description Generator: Prompts + SOP (Free Template)

Bydelmich

Reconciliation sounds like an accounting term, and it kind of is — but the task comes up everywhere. You have a customer list from your CRM and another from your billing system, and you need to know which records match, which are missing, and where the data disagrees. Or you’ve exported the same report from two different tools and need to verify they’re telling you the same story.

I’ve done this enough times to know that the “just eyeball it” approach stops working once you’re past about 50 rows. After that, you need formulas. Here are three methods I use, ranked from simplest to most powerful. Each one fits different situations, so pick the one that matches your data.

Before You Start: Prep Your Data

Every reconciliation method depends on having a reliable key — a column that uniquely identifies each row. This is usually an email address, an order ID, a SKU, or an employee number. Before you start matching, make sure your keys are clean:

=LOWER(TRIM(A2))

Run this on both sheets. I can’t count the number of times a reconciliation “failed” because one sheet had john@example.com (trailing space) and the other had John@Example.com (mixed case). Normalizing first eliminates these false mismatches.

If no single column is unique (say you have two “John Smith” entries), create a composite key by concatenating multiple columns:

=A2 & "|" & B2

For example, combining email and company name: john@example.com|Acme Corp. Use a separator character like | that won’t appear in your actual data.

Method 1: VLOOKUP / XLOOKUP (Quickest Setup)

This is the simplest approach and the one I reach for when I just need to pull a value from Sheet B into Sheet A based on a matching key.

The scenario: You have a list of orders in Sheet A and want to pull in the payment status from Sheet B, matching on order ID.

Google Sheets:

=VLOOKUP(A2, SheetB!A:C, 3, false)

This looks up the value in A2 within the first column of Sheet B’s range A:C, and returns the value from the 3rd column. The false parameter means it requires an exact match — you almost always want this.

Excel (365 or 2021+):

=XLOOKUP(A2, SheetB!A:A, SheetB!C:C, "not found", 0)

XLOOKUP is the modern replacement for VLOOKUP and it’s genuinely better in every way. You specify the lookup column and return column separately (so you’re not counting columns), and you can provide a fallback value for when there’s no match. The 0 at the end means exact match.

When to use this method: When you need to enrich one sheet with data from another — pulling in a status, a price, a category, or any other field by matching on a shared key. It’s fast to set up and easy to understand.

The limitation: VLOOKUP can only look to the right. If your key column isn’t the leftmost column in your range, it won’t work. XLOOKUP doesn’t have this limitation, which is one reason it’s the better choice if your version of Excel supports it.

Method 2: INDEX-MATCH (The Reliable Workhorse)

INDEX-MATCH does the same thing as VLOOKUP but without the column-position dependency. It works in every version of Sheets and Excel, and it handles edge cases better. This is the formula I recommend if you’re going to memorize one lookup pattern.

=INDEX(SheetB!C:C, MATCH(A2, SheetB!A:A, 0))

Here’s what’s happening: MATCH(A2, SheetB!A:A, 0) finds the row number where A2 appears in Sheet B’s column A. Then INDEX(SheetB!C:C, ...) returns the value from that row in column C.

The advantage over VLOOKUP is flexibility. Your return column can be anywhere — to the left, to the right, on a different sheet. You specify each column independently.

Adding error handling:

When a key in Sheet A doesn’t exist in Sheet B, MATCH throws a #N/A error. Wrap the whole thing in IFERROR to handle this gracefully:

=IFERROR(INDEX(SheetB!C:C, MATCH(A2, SheetB!A:A, 0)), "")

An empty string is usually the best fallback for reconciliation work — it makes it immediately obvious which rows didn’t match when you scan the column. You can also use "NOT FOUND" or "CHECK" as the fallback if you want a more visible flag.

When to use this method: When you need a reliable, portable formula that works everywhere. I use this as my default for any lookup that’s going to stay in the spreadsheet for ongoing use, because it’s more maintainable than VLOOKUP — if your column structure changes, you only update the column reference, not a numeric index.

Method 3: QUERY Join (Google Sheets Power Move)

The QUERY function in Google Sheets uses a SQL-like syntax that’s incredibly powerful for combining data from multiple ranges. If you’re comfortable with SQL, this will feel natural. If you’re not, it has a learning curve — but it’s worth it for complex reconciliation.

Left join example: Combine all rows from Sheet A with matching data from Sheet B, keeping every row from A even if there’s no match in B:

=QUERY({SheetA!A:D, SheetB!B:B},
       "SELECT Col1, Col2, Col3, Col4, Col6 WHERE Col1 IS NOT NULL", 1)

A few important notes about this approach:

The curly braces {} create a combined array by stacking the ranges side by side. When you do this, the column references change to Col1, Col2, Col3... instead of letter references. Col6 in this example refers to the first column of the second range (Sheet B’s column B).

This approach requires that both sheets are sorted by the same key and have the same number of rows for the side-by-side alignment to work. If they don’t, you’ll get mismatched rows. For datasets where the rows don’t align naturally, combine QUERY with VLOOKUP or INDEX-MATCH to pull in the matched values first, then use QUERY for filtering and aggregation.

When to use this method: When you need to filter, aggregate, or reshape reconciliation results — for example, “show me only the rows where the amounts don’t match” or “sum the discrepancies by category.” QUERY excels at this kind of analytical follow-up.

Highlighting Differences with Conditional Formatting

Once you have your match formulas in place, visual highlighting makes it much faster to spot problems. Here’s how to set up color-coded flags:

Rows in A but not in B (highlight in red):

  1. Select your key column in Sheet A.
  2. Go to Format → Conditional formatting.
  3. Set the condition to Custom formula: =ISNA(MATCH(A2, SheetB!A:A, 0))
  4. Choose a red fill color.

Rows in B but not in A (highlight in blue):

Same process on Sheet B, with the formula flipped:

=ISNA(MATCH(A2, SheetA!A:A, 0))

Values that exist in both but disagree (highlight in yellow):

If you’ve already pulled in the comparison value via lookup, you can highlight cells where the two values don’t match:

=AND(A2<>"", C2<>"", A2<>C2)

Apply this to the column where your lookup results appear. Yellow-highlighted cells are your discrepancies — the rows that exist in both sheets but have different data.

Reconciliation Checklist

Before you call the reconciliation “done,” run through these checks:

Are your keys truly unique? Run =COUNTIF(A:A, A2) and look for values greater than 1. Duplicate keys cause lookups to return the first match silently, which means you could be pulling in the wrong data without realizing it.

Did you normalize before matching? Apply =LOWER(TRIM(A2)) to both key columns. Case differences and trailing spaces are the number one cause of phantom mismatches.

Are you comparing the same data types? A cell formatted as a number and a cell formatted as text won’t match even if they display the same value. Use =ISNUMBER(A2) to check, and convert with =VALUE(A2) or =TEXT(A2, "0") as needed.

Have you accounted for missing data? Blank cells can cause unexpected behavior in lookup formulas. Adding IF(A2="", "", ...) wrappers prevents formulas from running against empty rows.

FAQ

Which method is fastest for large datasets? For pure performance on large ranges (10,000+ rows), INDEX-MATCH is typically faster than VLOOKUP because it doesn’t need to scan columns to the right. QUERY can be slower on very large datasets because it processes the entire array. That said, for most real-world reconciliation tasks, the performance difference is negligible.

What causes matches to fail even when the values look identical? The most common culprits: trailing spaces, non-breaking spaces (character 160, often from web-copied data), mismatched data types (text vs. number), and Unicode lookalike characters. The cleanup formula =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) catches most of these.

Can I reconcile more than two sheets at once? Yes, but do it in stages. Reconcile A against B first, then reconcile the result against C. Trying to match three sources simultaneously in one formula is possible but becomes very hard to debug when something doesn’t match.

Should I use Power Query in Excel instead? If you’re working in Excel and dealing with recurring reconciliation (same format, different data each month), Power Query is a much better tool than formulas. You set up the merge logic once, and then just refresh when new data arrives. It handles data type mismatches, null values, and large datasets far more gracefully than worksheet formulas.

Similar Posts