One sheet holds product codes, another holds the price list, and you need the price pulled across. Your VLOOKUP returns #N/A on half the rows and you cannot tell why.
How to use VLOOKUP Formula step by step
1. Choose the function
VLOOKUP is the classic, XLOOKUP the modern one (Excel 2021 and 365), and INDEX+MATCH works in any version and can look to the left.
2. Give the key and the table
Enter the cell with the value to find and the range. With VLOOKUP the column number counts from the first column of the table.
3. Decide what to show when nothing matches
A text such as "Not found" keeps #N/A out of your report. Copy the formula and drag it down.
The Step Count: Traditional Friction vs. KorexTools
Building VLOOKUP by hand and counting columns (4 steps)
- Type the function and the value to find.
- Count columns up to the one you want back.
- Remember to add FALSE at the end.
- Wrap it in IFERROR to clear the #N/A.
With the KorexTools “VLOOKUP Formula” builder (3 steps)
- Pick VLOOKUP, XLOOKUP or INDEX.
- Give the key, table and column.
- Copy the lookup and drag it to the last row.
The three reasons VLOOKUP answers #N/A even though the value is there
First, VLOOKUP only looks in the first column of the table; if the key sits elsewhere, it never sees it. Second, the approximate match Excel falls back on when you omit the last argument needs a sorted table and silently returns wrong data. Third, and most common, the key is a number on one side and text on the other, even though they look identical on screen.
The builder writes VLOOKUP with an exact match by default, offers XLOOKUP and INDEX+MATCH as alternatives that do not rely on counting columns, and wraps the result in IFERROR when you give it a message. The report stops filling with #N/A, and the formula is ready for your Excel’s separator and language.
VLOOKUP, XLOOKUP or INDEX+MATCH: which one to use
- •Older Excel and the key in the first column: VLOOKUP with an exact match.
- •Excel 2021 or 365: XLOOKUP, which counts no columns and searches in any direction.
- •Key to the right of the data, or a workbook shared with old versions: INDEX+MATCH.
One lookup, three formulas
| Function | Formula | Looks left |
|---|---|---|
| VLOOKUP | =VLOOKUP(A2,Sheet2!A:C,3,FALSE) | No |
| XLOOKUP | =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C) | Yes |
| INDEX+MATCH | =INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0)) | Yes |
Common Mistakes and How to Avoid Them
- ⚠️Passing a range that does not begin at the key column.
- ⚠️Leaving the range relative so the table drifts when you drag the formula; anchor it with $.
Frequently Asked Questions
What column number should I use in VLOOKUP?
The column you want returned, counted from the first column of the table range. If the table is A:C and you want C, the number is 3.
Why do I get #N/A when I can see the value in the table?
Usually one side is text and the other a number, or there is a trailing space. Try TRIM on the key, or convert both to the same type.
Should I keep the exact match?
Almost always. The approximate match only makes sense with sorted tables of bands, such as commission tiers or grade scales.
How do I do a VLOOKUP between two sheets?
Type the sheet name before the table range, such as Prices!A:C, or in single quotes if it has spaces, like 'List 2025'!A:C. The generated formula keeps that prefix and behaves exactly as it does on a single sheet.
What is VLOOKUP used for in Excel?
Pulling a value that lives in another table onto your row by using a shared key: the price for a code, a customer’s name from their ID or the commission that matches a band.
Does VLOOKUP accept wildcards or partial text?
Yes, with exact match. Replace the key in the generated formula with "*"&E2&"*" and the asterisk will allow any text before and after; the question mark allows a single character.
How do I combine VLOOKUP with IF or with two lookup values?
For IF, place the VLOOKUP inside the comparison. VLOOKUP cannot take two keys at once: for that use the two-criteria variant of the INDEX and MATCH tool in this same suite.
What I would do if I were in your shoes
«Before trusting a lookup, test it with a key you know does not exist: if your not-found message never appears, the formula is not doing what you think.» — DataKorex Engineering Team.