How to do a VLOOKUP without Excel

What a VLOOKUP does

Say you have an order list and a customer list:

Order	Customer ID	Total
1041	C-207	86.00
1042	C-113	42.50
1043	C-555	19.99

Customer ID	Name	Email
C-113	Ana Ruiz	ana@example.com
C-207	Lee Park	lee@example.com

You want the name and email next to each order. In Excel that's =VLOOKUP(B2, Customers!A:C, 2, FALSE) for the name, another formula for the email, filled down every row. Order 1043 shows #N/A because C-555 isn't in the customer list.

Database people call this a left join: keep every row of the first list, and add columns from the second wherever the key matches.

Doing it with FileMixer

1. Open the join tool.
2. Add the order list as the first file (the rows you keep) and the customer list as the second file.
3. Pick Customer ID as the matching column in each. If both files use the same column name, it's picked for you.
4. Leave Every row of the first file selected. Uncheck any columns you don't need from the customer list.
5. Read the note above the preview: “2 of 3 rows in the first file found a match. 1 did not.” Then download.

Unlike VLOOKUP, the key column doesn't need to be the first column, you bring all the columns in one go, and the result is plain values, not formulas that break when someone moves the source file.

Open the join tool

The five reasons rows don't match

When a lookup misses rows you know are there, the values differ in a way you can't see. In order of how often we run into them:

1. Spaces. “C-207 ” with a trailing space isn't “C-207”. FileMixer's Ignore capitals and extra spaces option handles this. In Excel, wrap both sides in TRIM().
2. Capitals. “ANA@EXAMPLE.COM” and “ana@example.com”. Excel's VLOOKUP already ignores case; FileMixer does with the same option on.
3. Leading zeros. One system exports 00123, the other 123. Pick one form and fix the other file, for example with the clean tool's find and replace, or by opening the file with the column set to Text (see leading zeros).
4. Invisible characters. Data copied from a web page or PDF often carries non-breaking spaces or zero-width characters. Run the file through the clean tool with “Remove invisible characters” on.
5. Numbers stored as text. In Excel, 123 the number and "123" the text don't match. FileMixer compares everything as text, so this one doesn't bite.

To see exactly which rows failed, choose Only rows of the first file with no match in the join tool.

When the second list has the same key twice

VLOOKUP quietly takes the first match. FileMixer does the same by default and tells you how many keys repeat. If you want every match, say, all the orders for each customer, choose Repeat the row for every match.

Other kinds of join

You want	Choose
Every order, with customer details where known	Every row of the first file
Only orders from known customers	Only rows that match
Everything from both lists, matched where possible	Every row of both files
Orders whose customer is missing from the list	Only rows of the first file with no match