VLOOKUP looks for a value in the first column of a range and returns a value from another column in the same row. The formula is =VLOOKUP(lookup_value, table_array, col_index_num, FALSE), so =VLOOKUP(A2, Vendors!A:C, 3, FALSE) takes the ID in cell A2, finds it in column A of the Vendors sheet, and returns whatever sits in the third column of that range. Always end with FALSE. That forces an exact match, and it is the single setting that separates a lookup you can trust from one that quietly returns the wrong row.
Most people meet VLOOKUP the moment they have two spreadsheets that should agree and do not. You convert a PDF statement or a supplier report into Excel, and now you have a column of account numbers, invoice references, or item codes that need names, categories, or prices attached from a master list. VLOOKUP is the function that stitches those two sheets together.
The four arguments, in plain English
VLOOKUP takes four arguments and the fourth is the one everybody skips.
| Argument | What it means | Example |
|---|---|---|
| lookup_value | The thing you are searching for | A2 (an invoice number) |
| table_array | The range to search. Its FIRST column must contain the lookup value | Vendors!A:C |
| col_index_num | Which column of that range to return, counting the first column as 1 | 3 (the third column) |
| range_lookup | FALSE for an exact match, TRUE for an approximate one | FALSE |
Two rules follow from that table and they cause most of the pain. First, the lookup value has to live in the leftmost column of the range you hand to VLOOKUP. It cannot look to its left. If your IDs are in column C and the names you want are in column A, VLOOKUP will not do it. Second, col_index_num counts from the start of your range, not from column A of the sheet. If your range is B:E, then column 1 is B, column 2 is C, and so on. Counting from the wrong place is the most common way to get a real value back that happens to be the wrong value.
A worked example
Say you converted a purchase report out of a PDF and landed with item codes in column A and quantities in column B, and you keep a price list on a second sheet named Prices with codes in column A and unit prices in column B.
In C2 you write =VLOOKUP(A2, Prices!$A$2:$B$500, 2, FALSE) and fill it down. Excel takes the code in A2, hunts for it in Prices column A, and hands back the price sitting next to it. Note the dollar signs. Locking the range with $A$2:$B$500 means the range stays put as the formula copies down; without them the range slides down a row each time and the last rows of your list quietly fall out of scope.
To turn that into a line total you nest it: =B2 * VLOOKUP(A2, Prices!$A$2:$B$500, 2, FALSE). To keep a missing code from spraying errors across the sheet, wrap it: =IFERROR(VLOOKUP(A2, Prices!$A$2:$B$500, 2, FALSE), "Not found").
Why does VLOOKUP return #N/A when the value is clearly there?
#N/A means VLOOKUP searched and did not find the value, so with FALSE set it gives up rather than guess. Microsoft lists the usual causes: the value genuinely is not in the first column of the range, or the two cells look identical but are not, because of a trailing space, a non printing character, or a number stored as text on one side and as a real number on the other.
That last one is the classic after a document conversion. A converter can hand you invoice numbers that Excel treats as text while your master list holds them as numbers, and 10045 as text will never match 10045 as a number. Three checks fix nearly every case:
| Symptom | Cause | Fix |
|---|---|---|
| #N/A on every row | Data types differ (text vs number) | Convert the column: see how to convert text to number in Excel |
| #N/A on some rows only | Stray spaces or hidden characters | =TRIM(A2) or =SUBSTITUTE(A2, CHAR(160), "") to strip a non breaking space |
| #N/A but the value is visible | The value is not in the range's FIRST column | Move the column, or switch to XLOOKUP or INDEX MATCH |
| Wrong value returned, no error | col_index_num counted from column A instead of from the range | Recount from the first column of table_array |
| Values drift as you fill down | The range is not locked | Add dollar signs: $A$2:$B$500 |
A quick diagnostic: put =ISNUMBER(A2) next to your lookup value and =ISNUMBER(Prices!A2) next to the master value. If one says TRUE and the other says FALSE, you have found your problem.
What is the difference between TRUE and FALSE in VLOOKUP?
FALSE demands an exact match and returns #N/A if it cannot find one. TRUE returns the closest match that is less than or equal to your lookup value, and it assumes the first column is sorted in ascending order. If it is not sorted, TRUE returns nonsense without a single error message. For matching IDs, invoice numbers, account codes, or names, always use FALSE. TRUE is only for genuine banded lookups, like mapping an income figure to a tax bracket.
Leaving the fourth argument out entirely is the same as writing TRUE, which is why an unfinished VLOOKUP can return numbers that look plausible and are wrong. That is the failure mode worth fearing, because nothing on screen tells you it happened.
Is XLOOKUP better than VLOOKUP?
For new work, yes, if you have it. XLOOKUP defaults to an exact match, so there is no fourth argument to forget. It can look left as easily as right, so the lookup column does not have to be leftmost. It takes a lookup array and a return array instead of a column number, so inserting a column in the middle of your data does not silently break the formula. And it has a built in fallback: =XLOOKUP(A2, Prices!A:A, Prices!B:B, "Not found").
The catch is availability. XLOOKUP ships with Microsoft 365 and Excel 2021 and later. On older installs, and in files you will share with people on older installs, VLOOKUP still rules. The portable middle ground is INDEX MATCH, which works everywhere and also looks in any direction: =INDEX(Prices!B:B, MATCH(A2, Prices!A:A, 0)). The 0 in MATCH is the exact match flag, playing the same role FALSE plays in VLOOKUP.
| VLOOKUP | XLOOKUP | INDEX MATCH | |
|---|---|---|---|
| Exact match by default | No, you must pass FALSE | Yes | No, you must pass 0 |
| Can look to the left | No | Yes | Yes |
| Survives an inserted column | No | Yes | Yes |
| Works in older Excel | Yes | 365 and 2021 and later | Yes |
| Easiest to read | Yes | Yes | Least |
How do I VLOOKUP between two sheets?
Point table_array at the other sheet by name: =VLOOKUP(A2, Prices!$A$2:$B$500, 2, FALSE). If the sheet name contains a space, Excel wraps it in single quotes automatically, as in 'Price List'!$A$2:$B$500. The cleanest way to build it is to type =VLOOKUP(A2, then click the other sheet's tab and drag over the range with the mouse, so Excel writes the reference for you and you cannot fat finger the name.
Looking up against another workbook works the same way but the file has to stay open, or the reference becomes a long file path that breaks when the file moves. For anything you will keep, copy the master list into the same workbook on its own sheet.
Where VLOOKUP fits after a PDF conversion
The reason this function matters so much in a document workflow is that a converted file is only half the job. You get rows out of the PDF, but the rows arrive with codes, not meaning. VLOOKUP is what attaches vendor names to account numbers, categories to transaction descriptions, and prices to item codes, and it is how you check that the file you converted matches the system of record.
That only works if each field landed in its own column to begin with. If everything came through in column A, VLOOKUP has nothing to look up, and you need to fix the structure first: split the data apart with Text to Columns, or convert the file again with a PDF to Excel converter that reads the table structure rather than the raw text. If the codes are drifting in from a stack of supplier documents, it is faster to pull the line items into a spreadsheet automatically than to retype them and hope the references match.
Once the lookup runs clean, do the arithmetic check: sum the amount column and confirm it ties to the printed total on the original PDF. A VLOOKUP that returns values for every row still proves nothing if half the rows never made it out of the document.
The short version
Use =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) and never omit the FALSE. Keep the lookup value in the first column of the range, lock the range with dollar signs, and count col_index_num from the start of the range rather than from column A. If you get #N/A, check for numbers stored as text and stray spaces before you blame the formula. On Excel 2021 or Microsoft 365, XLOOKUP does the same job with fewer ways to get it wrong.