VLOOKUP between two files

Your main file goes on the left and the reference table on the right. Say which column identifies the same thing in both, tick the columns you want brought over, and every row of your file comes back with the extra columns attached. Rows that found nothing keep their place and get blanks, or a marker you choose. No formula to drag down.

What VLOOKUP is for, and where it hurts

The job is always the same. You have a file of things that happened, and a separate list describing the things they happened to. Orders and customers. Line items and products. Timesheets and employees. Transactions and account names. You need the description attached to the event, and the only thing connecting them is an id.

In a spreadsheet that is a formula, and the formula has a list of well-known ways to ruin an afternoon. It has to be dragged down the whole column, and if the range is not locked, it quietly stops looking at the right rows partway down. It returns #N/A with no explanation of which side is at fault. It breaks the moment somebody inserts a column in the lookup table. It gets slower with every row until a file of any real size makes the whole workbook unresponsive. And when it has finished, you still do not know how many rows actually found a match, because counting them is another formula.

This page is the same operation with none of that. Two files, one statement of what matches what, and a result. The count you wanted is printed above it without being asked for.

Worked example: attaching customer names to orders

Ten orders, and a customer list of six. Click Try with sample data and both are loaded for you. Set Match rows on to customer_id on both sides, and type name, country under Columns to bring over. Out comes:

order_id,customer_id,order_date,region,product,quantity,amount,name,country
1001,C001,2024-01-05,East,Widget,3,120.50,Ada Lovelace,United Kingdom
1002,C002,2024-01-06,West,Gadget,1,88.00,Grace Hopper,United States
1003,C001,2024-01-11,East,Thing,2,240.00,Ada Lovelace,United Kingdom
...
1009,C009,2024-03-21,West,Gadget,3,132.40,,

Ten orders in, ten orders out. That is the guarantee: a lookup never loses a row of your main file, which is why the join type is fixed here rather than offered as a dropdown. Order 1009 belongs to a customer who is not in the list, so its two new columns are empty and the report above says unmatched A 1.

Now type #N/A into When there is no match, write and run it again. Row 1009 gets #N/A,#N/A instead of two empty cells. This matters more than it looks: an empty cell in a CSV is ambiguous, because it might mean the lookup failed or it might mean the customer genuinely has no country recorded. A marker separates the two, and it survives into whatever opens the file next.

How this differs from the join page

Same engine underneath. The differences are all about removing choices you should not have to make for this particular job:

The join type is pinned to left and there is no dropdown for it. A lookup that drops rows of your main file is not a lookup, it is a filter you did not ask for, and having the control there at all invites somebody to click Inner and lose forty rows without noticing.
The slots are labeled Your main file and Lookup table rather than A and B, because in this operation they genuinely are different kinds of thing and mixing them up is the commonest first mistake.
There is an unmatched marker box, which the join page does not have, because #N/A is what people coming from a spreadsheet expect a failed lookup to look like.
The collision suffix defaults to _lookup rather than _b, so a clash reads as country_lookup and it is obvious which file it came from.

If you want the other join types, the anti join or the equivalent SQL, the join page is the same engine with everything exposed.

Why lookups fail, in order of how often

When the match report says a number you did not expect, it is nearly always one of these, roughly in frequency order:

One file typed the ids as numbers. A spreadsheet opens 00123 and stores 123. The other system kept the string. Nothing matches, and both columns look correct on screen. Set Numbers to 1.0 = 01 = 1.
Trailing spaces. Fixed-width exports pad every field. Already handled by default here, which is why this page finds matches that a spreadsheet formula does not.
Case. One system uppercases, another does not. One toggle.
The lookup table is genuinely incomplete. Sometimes the answer is that the reference list has not been updated. The match report is what tells you this is the case rather than a technical problem, and the anti join gives you the exact list of missing ids to send on.
It takes two columns to identify a row. Region alone is not unique; region and month is. Add a second key pair.

Things a spreadsheet lookup cannot do

Match on several columns at once. Classic VLOOKUP takes one key. Doing it on two means building a helper column that concatenates them, in both files, and hoping nothing in the data contains your separator. Here it is a second row of dropdowns.
Bring several columns over in one pass. VLOOKUP returns one column per formula, so five columns is five formulas with five different index numbers, all of which break when the lookup table gains a column. Here it is a list of names.
Tell you the hit rate. Counting the matches is another formula, and by the time you have written it you have usually stopped caring. It is printed here whether you ask or not.
Handle a file with a million rows. A worksheet stops at 1,048,576 rows, and a lookup formula on even a fraction of that makes the file unusable. This runs the match once, in one pass, with no formula recalc.
Read the other file without opening it. A cross-workbook VLOOKUP needs both files open and the link intact. Here the second file can be an Excel workbook, a JSON export or a TSV, and it is read once.

Frequently Asked Questions

Is this the same as VLOOKUP or XLOOKUP?

It does the same job. You point at a value in your file, point at where to find it in another table, and say what to bring back. The differences are all in your favor: there is no formula to drag, no column index to break when someone inserts a column, no range to lock, and the match count is printed for you. It is closer to XLOOKUP than VLOOKUP in that the key does not have to be the leftmost column of the lookup table.

Will I lose rows from my main file?

No. The join type is fixed at left specifically so that cannot happen. Every row of your main file comes out, in the same order, whether or not it found a match. The report above the result confirms it: rows out always equals A rows unless your lookup table has duplicate keys, and if it does, that is called out in a warning.

What if my lookup table has two rows with the same id?

You get both, which is what a database would do, and the warning above the result tells you how many extra rows that produced. That is usually a sign the reference table needs deduplicating. Run it through the deduplicate tool first if you want one row per key, or use the Semi join on the join page if you only needed to know which ids were present.

Can I make failed lookups say #N/A instead of being blank?

Yes, that is what the marker box is for. Type anything you like: #N/A, not found, a hyphen. It is written into every column that was brought over on rows that found no match, which distinguishes a failed lookup from a value that is genuinely empty in the reference table. Leave it empty and you get blanks.

Do the two files have to be the same format?

No. One can be a CSV and the other an Excel workbook, or a JSON export, or JSON Lines. The tool reads each on its own terms. If a workbook has several sheets with data in them, the one with the most rows is used and the page says which one, rather than taking the first sheet without mentioning it.

How big can the files be?

100 MB each. Within that there is no row limit, no sign-up and no daily allowance. A lookup across a few hundred thousand rows finishes in about a second because the reference table is indexed once and then read, rather than scanned again for every row the way a spreadsheet formula does it.

Do the lookup without the formula

Two files, one statement of what matches what, and a count of how many rows actually found a partner.

Back to the lookup tool