Excel guide · lookups
Match two Excel lists without VLOOKUP
VLOOKUP finds a code in one list and returns the value from the same row, such as a price for a product code. This page shows how the formula works and where #N/A comes from, then matches two lists in Nexell by typing instead. For the full syntax of the function, see the VLOOKUP function reference.
Free account · no card · 30 credits a month · Not ready to sign up? Paste some text and try it
Step by step
How a VLOOKUP formula is written
-
1
Pick the lookup value
In the cell for the result type =VLOOKUP( and click the cell with the value to find, here the item code in A2. In English Excel the parts of the formula are separated by commas.
-
2
Give the lookup range
The second part is the range whose first column holds the lookup value: October!$A$2:$C$7. If the list is on another sheet, put the sheet name and an exclamation mark in front, and lock the range with F4 because you will copy the formula down.
-
3
Count the column
The third part is the position of the column to return inside the range. The range starts in column A, so the price in column C is column 3.
-
4
Ask for an exact match with FALSE
Type FALSE as the last part. Left empty, Excel matches approximately and can return another row's value. A missing code shows #N/A. You can also look up with XLOOKUP, which needs no column count.
Example
Two price lists matched by item code, with no formula
We dropped two price lists into the tool as CSV files and typed a single request.
1Two files
2In Nexell
- Two CSV files read and stacked into one table: 12 rows. The Source column says which file each row came from.
- compare by item codeMatched on the Item Code column: 5 matched, 1 only on the left, 1 only on the right. Difference and change columns added for Unit Price
The table changed only after Apply.
3In Excel
Only in september.csv| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Item Code | Product | Unit Price | Product (october.csv) | Unit Price (october.csv) | Unit Price difference | Unit Price change % | Match |
| 2 | KBL-1525 | Cable 1.5 mm (per metre) | 48.50 | Cable 1.5 mm (per metre) | 52.00 | 3.50 | 7.22 | In both |
| 3 | KBL-1540 | Cable 2.5 mm (per metre) | 71.20 | Cable 2.5 mm (per metre) | 71.20 | 0.00 | 0.00 | In both |
| 4 | PRZ-0210 | Wall socket (white) | 86.00 | Wall socket (white) | 92.50 | 6.50 | 7.56 | In both |
| 5 | SGT-0016 | Circuit breaker | 132.00 | Circuit breaker | 132.00 | 0.00 | 0.00 | In both |
| 6 | LMB-0918 | LED bulb (warm white) | 39.90 | Only in september.csv | ||||
| 7 | KNL-2040 | Cable trunking | 64.00 | Cable trunking | 68.00 | 4.00 | 6.25 | In both |
| 8 | ANH-0101 | Light switch (single) | Light switch (single) | 54.00 | Only in october.csv |
The difference and the change percentage are written as numbers (7.22 means a 7.22% increase). The Match column also names the rows where VLOOKUP would give #N/A: the LED bulb is only in September and the light switch only in October. Cost: 2 credits.
Illustrative example with made-up products and prices; the layout shown is what Nexell's comparison produces in the downloaded file.
Why Nexell
What Nexell does differently
Both directions at once
VLOOKUP looks one way. Say “compare, which side is missing” and the records in both lists and in only one of them appear in the same table.
No column counting
You name the shared column; you never count the column position as VLOOKUP asks. AI reads the request, so you can write it in many languages.
Values checked against your files
Every value the AI writes into the table is looked up in the text of your files. A value that cannot be found exactly is not deleted; it is flagged in the Check column.
Limits
What to know before you start
- The match may not stay a formula. Matched values are written into the cells as values, so the file may contain no VLOOKUP formula. If one of the lists changes, run the match again.
- Check the match in the preview. The Check column only tells you whether a value occurs in your files, not that it was matched to the right row; look at a few rows before you click Apply.
- Privacy. Personal data in text is masked before it is sent to the AI service (what is sent).
FAQ
VLOOKUP and matching two lists: common questions
How do I pull data from another sheet with VLOOKUP?
Put the sheet name and an exclamation mark in front of the lookup range: =VLOOKUP(A2,October!$A$2:$C$7,3,FALSE). If the sheet name contains a space, wrap it in single quotes, such as 'October Prices'!$A$2:$C$7.
Why does VLOOKUP return #N/A?
The lookup value was not found in the first column of the range. Common reasons: the code is not in the list; an invisible trailing space (clean it with TRIM); or a number in one list and text in the other. IFERROR turns the error into a message of your choice.
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP takes the range to return instead of a column number, can return values to the left of the lookup column, matches exactly by default and lets you say what to show when nothing is found. It exists in Microsoft 365 and Excel 2021 and later; in older versions use VLOOKUP, or INDEX with MATCH.
Does Nexell write a VLOOKUP formula when it matches two lists?
Usually not: the matched rows are written side by side as values, so the file has no VLOOKUP formula. You can also ask for the difference and the percentage change when you compare. If one of the lists changes, run the match again.
Related
More Excel jobs you can do by typing
- Pivot table in ExcelTotal a long list by region, month or product, step by step.
- Conditional formattingColor the rows that cross a threshold.
- Excel formula generatorType what to calculate and let Nexell build the formula.
- VAT calculation in ExcelSplit a gross amount into net and VAT with live formulas.
Stop comparing two lists row by row.
Create a free account, bring your data and type what you need.
Try it free