Skip to content

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. 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. 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. 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. 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

  1. Two CSV files read and stacked into one table: 12 rows. The Source column says which file each row came from.
  2. 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

H6fxOnly in september.csv
The Data sheet of the downloaded Excel file: the two lists side by side by item code
ABCDEFGH
1Item CodeProductUnit PriceProduct (october.csv)Unit Price (october.csv)Unit Price differenceUnit Price change %Match
2KBL-1525Cable 1.5 mm (per metre)48.50Cable 1.5 mm (per metre)52.003.507.22In both
3KBL-1540Cable 2.5 mm (per metre)71.20Cable 2.5 mm (per metre)71.200.000.00In both
4PRZ-0210Wall socket (white)86.00Wall socket (white)92.506.507.56In both
5SGT-0016Circuit breaker132.00Circuit breaker132.000.000.00In both
6LMB-0918LED bulb (warm white)39.90Only in september.csv
7KNL-2040Cable trunking64.00Cable trunking68.004.006.25In both
8ANH-0101Light switch (single)Light switch (single)54.00Only in october.csv
DataChart

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.

Stop comparing two lists row by row.

Create a free account, bring your data and type what you need.

Try it free