A VLOOKUP Alternative That Works Without Opening Excel

VLOOKUP does one specific job well: find a row in one table by matching a key, and pull a value from it into another table. It's also fussy - column indexes have to be counted by hand, exact versus approximate match is easy to get wrong, and a single mismatched key produces a bare #N/A with no explanation. If what you actually have is two exported files rather than a live spreadsheet, there's a simpler path: paste both, pick the key columns, and let the matching happen automatically.

Quick answer

Paste both datasets into Column Compare, tell it which column in each is the key (and optionally which column holds a value to compare), and it returns four groups instantly: Matched rows, rows where the value changed, keys only in dataset A, and keys only in dataset B. No formulas, no column-index counting, no #N/A cells to filter out afterward.

Match two datasets by key column now. Free, no account needed.

Open Column Compare →

What VLOOKUP is actually doing

Strip away the formula syntax and VLOOKUP is a row-matching operation: given a value, find the row in another table where a specified column equals that value, then return a value from a different column in that same row. That's exactly the shape of "compare two datasets by a key and pull the matching data" - which is a common enough task that it deserves a tool built around it directly, rather than a formula bolted onto a spreadsheet.

Why it gets painful in practice

A few things make VLOOKUP more error-prone than it should be. The column to return is specified by a raw index number counted from the left edge of the table array, so inserting a column anywhere in that range silently breaks every formula referencing a column past it. The fourth argument controls exact versus approximate matching, and leaving it out (or getting the default wrong) produces confidently incorrect results rather than an obvious error. And when a key simply doesn't exist in the other table, you get #N/A with no indication of how many rows failed to match or why, until you wrap it in IFERROR and start counting manually.

How Column Compare handles the same job

Paste both datasets - each can be CSV, TSV, or any consistently delimited text, with or without a header row. Two dropdowns let you pick the key column in each dataset (by header name if one exists), and optional value column dropdowns let you specify what you want to compare once keys match. The tool returns four clearly separated results:

Matched - keys found in both datasets.
Changed values - matched keys where the specified value column differs between the two datasets.
Only in A - keys present in the first dataset with no match in the second.
Only in B - the reverse: keys present in the second dataset with no match in the first.

There's no separate error state to interpret - unmatched keys are already sorted into their own category, and changed values are flagged rather than requiring a second lookup formula layered on top of the first.

Matching options

Delimiter detection handles comma, tab, pipe, and semicolon automatically, with a manual override if your export uses something else. Case sensitivity and whitespace normalization for the key column are both configurable, which matters when the two datasets come from different systems with inconsistent formatting conventions - one export trimming spaces, the other not.

Just need to compare two plain Excel columns?

See the simpler single-column comparison walkthrough.

Read the guide →

Frequently Asked Questions

Do I need Excel installed to use this?

No. Column Compare runs entirely in your browser - paste two datasets as CSV, TSV, or any delimited text and it works the same whether or not you have a spreadsheet program open.

What's the equivalent of VLOOKUP's lookup value and table array?

You paste your two datasets into two separate panels, then pick which column in each acts as the key (the equivalent of the lookup value and the first column of the table array). There's no column-index counting - you select columns by name if headers are present.

Does it handle Excel's #N/A error case?

There is no error state to handle - unmatched keys simply appear in the "Only in A" or "Only in B" result section, already separated out rather than scattered through your data as #N/A cells you then have to filter for.

Can it tell me when a value changed, not just whether the key matched?

Yes. If you specify a value column in addition to the key column, matched rows are further split into rows where the value is identical and rows where it changed - this is the step a plain VLOOKUP doesn't do on its own; you'd normally need a second formula to compare the looked-up value against the original.

What delimiter does it expect?

Comma, tab, pipe, and semicolon are all auto-detected, or you can set the delimiter manually if your data uses something unusual.

Do I need a header row?

No, but if your data has one, turn on the header option so column names show up in the dropdowns instead of column numbers - makes it much easier to pick the right key and value columns at a glance.

Can it match approximately, like VLOOKUP with the range_lookup argument set to TRUE?

No, matching is exact-value only (with optional case-insensitivity and whitespace normalization). For genuinely fuzzy or typo-tolerant matching between two lists, that's a different kind of problem the tool doesn't currently solve.

How large a dataset can I paste in?

There's no hard row limit built into the tool - it runs in your browser, so practical limits come from your device's memory rather than the tool itself. Very large datasets (tens of thousands of rows) will still process, just with a brief delay.