The VLOOKUP function in Excel finds a value in the first column of a table and returns the data in the same row from a column you choose — for example, type the item code "SP03" and the unit price of 38,000 VND (about 1.5 USD) appears automatically. It is the lookup function most often tested in Excel interviews for office, accounting, warehouse and sales jobs in Vietnam. This lesson covers the syntax, a price lookup example with a small sample table, choosing exact or approximate match, fixing #N/A, #REF! and #VALUE! errors, and when to switch to INDEX/MATCH or XLOOKUP.
By the end of this lesson, you will be able to:
- Write a correct 4-argument VLOOKUP formula and explain each argument.
- Look up unit prices by item code for a whole order table and fill the formula down without the range shifting, thanks to the $ sign.
- Choose the right match type: FALSE/0 for item codes, TRUE for a grading table based on score thresholds.
- Find and fix the causes of #N/A, #REF! and #VALUE!, including extra spaces and numbers stored as text.
- Rewrite the same lookup with INDEX/MATCH and XLOOKUP when you need to look to the left.
Want to learn office computing in the right order? VLOOKUP belongs to step 4 – lookups and data cleaning. See the 8-step office computing path (in Vietnamese): file management, professional Word documents, Excel basics, PivotTables, dashboards and a certificate exam.
What is VLOOKUP and when do you use it?
The V in VLOOKUP stands for "vertical". The function goes down the first column of a table range, stops at the row containing the value you are looking for, and returns the data in column N of that row. You use it whenever you have a "master table" (a price list, a staff list, a customer list) and another table that only has codes and needs more information filled in.
Photo: Modjou / Wikimedia Commons, CC BY-SA 4.0
Everyday situations for VLOOKUP
- Grocery shop: type an item code on a sales sheet and the item name and unit price appear.
- HR department: the timesheet has staff IDs, and you need the salary from the personnel file.
- Warehouse: compare received item codes with the product list to find codes not yet registered.
- Teacher: convert average scores into grades by threshold.
Two conditions for VLOOKUP to work
First, the value you look for must be in the first column of the range you select; VLOOKUP only returns data from columns to the right of it. Second, the value you look for and the value in the table must be exactly the same, including the data type: "SP03" with a trailing space, or the number 1001 stored as text, counts as different. These two rules explain most of the errors later in this lesson.
VLOOKUP syntax
The full formula is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Function names and argument order are the same in every language version of Excel, including Vietnamese.
| Argument | Meaning | Example |
|---|---|---|
| lookup_value | The value to find (usually the cell with the code) | F2 containing "SP03" |
| table_array | The master table range; its first column holds the codes | $A$2:$D$7 |
| col_index_num | Which column to return, counted from the first column of the range (first column = 1) | 4 = column D – Unit price |
| range_lookup | FALSE or 0: exact match; TRUE or 1: approximate match. Left empty = TRUE | FALSE |
Separators: comma or semicolon?
This lesson writes formulas with commas ,. If your computer uses Vietnamese regional settings (where the decimal separator is a comma), Excel uses semicolons ; between arguments instead: =VLOOKUP(F2;$A$2:$D$7;4;FALSE). Use the wrong one and Excel says "There's a problem with this formula". A quick check: type =VLOOKUP( and look at which separator the tooltip shows. Google Sheets also switches separators according to the spreadsheet's locale.
Never leave the fourth argument empty
If you leave range_lookup empty, Excel treats it as TRUE (approximate match). With an unsorted list of item codes, the formula still returns a perfectly reasonable-looking number — but it is the price of a different item, and no error appears. The rule for beginners: always type FALSE, and only use TRUE when you deliberately look up by threshold.
Worked example: look up the unit price by item code
A grocery shop has its price list in A1:D7 (Figure 2 shows someone entering data into a spreadsheet like this). The order table sits in columns F to I. You need to fill the unit price in column H and calculate the line total in column I. Prices are in Vietnamese dong (VND).
| Row | A – Item code | B – Item name | C – Unit | D – Unit price (VND) |
|---|---|---|---|---|
| 2 | SP01 | Rice, 5 kg bag | bag | 185,000 |
| 3 | SP02 | Cooking oil, 1 litre | bottle | 52,000 |
| 4 | SP03 | Fish sauce, 500 ml | bottle | 38,000 |
| 5 | SP04 | White sugar, 1 kg | pack | 27,000 |
| 6 | SP05 | Instant noodles (carton of 30) | carton | 115,000 |
| 7 | SP06 | Fresh milk, 1 litre | carton | 34,000 |
Photo: Mark Diamond / Wikimedia Commons, Public domain
Steps
- Click cell H2, type
=VLOOKUP(F2,$A$2:$D$7,4,FALSE)and press Enter. F2 is "SP03", so the result is 38,000. - Click cell I2 and type
=G2*H2. - Select H2:I2 and drag the fill handle (the small square at the bottom right) down to row 5.
- In I6, type
=SUM(I2:I5)for the order total.
| Row | F – Item code | G – Quantity | H – Unit price (VLOOKUP) | I – Line total |
|---|---|---|---|---|
| 2 | SP03 | 4 | 38,000 | 152,000 |
| 3 | SP01 | 2 | 185,000 | 370,000 |
| 4 | SP05 | 1 | 115,000 | 115,000 |
| 5 | SP06 | 6 | 34,000 | 204,000 |
| 6 | Total | 841,000 |
Check by hand: 4 × 38,000 = 152,000; 2 × 185,000 = 370,000; 1 × 115,000 = 115,000; 6 × 34,000 = 204,000. Adding up: 152,000 + 370,000 + 115,000 + 204,000 = 841,000 VND, which matches cell I6.
Why the table range needs $ signs
If you type A2:D7 without $, Excel changes it to A3:D8 when you fill down to row 3, then A4:D9 in row 4, and so on. The range slides down and loses SP01 at the top, so lower rows show #N/A even though the code is in the table. The $ sign "locks" the column and row: $A$2:$D$7 stays fixed when copied. Tip: select the range in the formula bar and press F4 to add the $ signs. Keep F2 relative, because each row must look up its own code.
Return another column by changing col_index_num
For the item name: =VLOOKUP(F2,$A$2:$D$7,2,FALSE) returns "Fish sauce, 500 ml". For the unit, change 2 to 3 and you get "bottle". Columns are always counted from the first column of the range, not from column A of the sheet: if the range is $B$2:$D$7, column D is column 3.
Exact match (FALSE) and approximate match (TRUE)
Exact match looks for a value that matches completely; if there is none, it returns #N/A. Approximate match looks for the largest value that does not exceed the lookup value, and requires the first column of the table to be sorted in ascending order. That sounds odd, but it is exactly how threshold lookups work: school grades, commission tiers, shipping fees by weight.
Example: a grading table by score
| Row | K – Score from | L – Grade |
|---|---|---|
| 2 | 0 | Weak |
| 3 | 5 | Average |
| 4 | 6.5 | Good |
| 5 | 8 | Very good |
| 6 | 9 | Excellent |
The formula =VLOOKUP(N2,$K$2:$L$6,2,TRUE), where N2 holds a student's score:
- Score 7.2 → the largest threshold not above 7.2 is 6.5 → Good.
- Score 8 → exactly the threshold 8 → Very good.
- Score 4.5 → only threshold 0 is not above it → Weak.
- Score 9.5 → threshold 9 → Excellent.
When approximate match goes wrong
If column K is not sorted in ascending order, Excel still returns a result, but it may be wrong with no error shown. If the score is below the first threshold (for example the table starts at 1 and the score is 0.5), the result is #N/A. That is why a threshold table always starts at the smallest possible value (0 here). One more point: on a computer with Vietnamese regional settings you type a score as 6,5; with US settings you type 6.5. Type it the wrong way and Excel reads it as text, breaking the lookup.
#N/A, #REF! and #VALUE! errors and how to fix them
These three errors cover almost every case of "VLOOKUP doesn't work". Read the error code before fixing anything: each code points to a different group of causes.
| Error | Common causes | Fix |
|---|---|---|
| #N/A | Not found: typo in the code, extra spaces, numbers stored as text, an unlocked range that slid down, approximate match below the first threshold | Check the code, use TRIM, convert the data type, add $ |
| #REF! | col_index_num is larger than the number of columns in the range (5 when the range is only A:D), or the referenced range was deleted | Recount the columns, reselect the range |
| #VALUE! | col_index_num is less than 1 (for example 0) or not a number | Change it to a column number of 1 or more |
Extra spaces: use TRIM
Data pasted from sales software, emails or websites often carries a trailing space: "SP03 " looks exactly like "SP03". Check with =LEN(F2): a 4-character code that returns 5 has an extra character. Fix it with =VLOOKUP(TRIM(F2),$A$2:$D$7,4,FALSE). If the spaces are in the master table, add a helper column =TRIM(A2), copy it and paste values over the code column. Web data sometimes contains non-breaking spaces (character 160) that TRIM does not remove; use =SUBSTITUTE(F2,CHAR(160),"") instead.
Numbers stored as text
Staff ID 1001 is a number in the master table, but the text "1001" in the timesheet (often with a small green triangle in the corner and left-aligned). They do not match, so you get #N/A. Fix: if the master table holds numbers, look up with =VLOOKUP(VALUE(F2),…); if it holds text, use =VLOOKUP(F2&"",…). The thorough fix is to make the whole column one data type with Data → Text to Columns → Finish.
Photo: Lha Charitable Trust / Wikimedia Commons, CC BY-SA 4.0
Hide errors neatly with IFERROR
When a missing code is normal (a customer types a new code), replace #N/A with a clear message: =IFERROR(VLOOKUP(F2,$A$2:$D$7,4,FALSE),"Code not found"). Be careful: IFERROR hides every error, including a #REF! caused by counting columns wrongly. Only wrap a formula in IFERROR once it works; Excel 2013 and later also have IFNA, which only catches #N/A and is safer. While you are learning, like the students in Figure 3, do not rush to hide errors — read them.
VLOOKUP's limits: INDEX/MATCH and XLOOKUP
VLOOKUP has three weaknesses: it only looks to the right (it cannot return a column to the left of the code column), the column number is typed in, so inserting a column into the table shifts the result, and with duplicate codes it only returns the first matching row. The two alternatives below fix the first two.
INDEX/MATCH: look to the left as well
MATCH finds the row position, and INDEX returns the value at that position. To find the code for "Cooking oil, 1 litre" (the code column is to the left of the name column, which VLOOKUP cannot handle):
=INDEX($A$2:$A$7,MATCH("Cooking oil, 1 litre",$B$2:$B$7,0)) → SP02.
The 0 in MATCH means exact match, like FALSE in VLOOKUP. Because the result column is given as a range rather than a number, inserting columns does not break it. This formula works in every version of Excel and in Google Sheets.
XLOOKUP: shorter, with a built-in "not found" value
=XLOOKUP(F2,$A$2:$A$7,$D$2:$D$7,"Code not found") returns 38,000 for SP03. XLOOKUP uses exact match by default, can look in either direction and has a built-in "if not found" argument. It is available in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web; Google Sheets also has XLOOKUP. Excel 2019, 2016 and older do not — opening the file on an older computer shows #NAME?.
Which one to learn first?
| Criterion | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| Looks to the left | No | Yes | Yes |
| Safe when inserting columns | No | Yes | Yes |
| Default when match type is left empty | Approximate (error-prone) | MATCH defaults to approximate, so type 0 | Exact |
| Excel versions | All | All | Microsoft 365, 2021+, web; Google Sheets |
Practical advice: learn VLOOKUP first, because older company files and hiring tests still use it most; learn INDEX/MATCH for left lookups; use XLOOKUP when you are sure everyone receiving the file has a recent Excel.
Frequently asked questions
Is VLOOKUP case-sensitive?
No. "sp03" and "SP03" are treated as the same. If you need case sensitivity, you have to combine it with EXACT, which is rare in office work.
Can I look up from another sheet?
Yes. Include the sheet name in the range: =VLOOKUP(F2,PriceList!$A$2:$D$7,4,FALSE). If the sheet name has spaces, put it in single quotes: 'Price list'!$A$2:$D$7.
What if codes are duplicated in the master table?
VLOOKUP returns only the first matching row and gives no warning. Before looking up, check for duplicates with =COUNTIF($A$2:$A$7,A2): a result above 1 means a duplicate code that needs cleaning.
Do I need to edit the formula when new rows are added to the master table?
With a fixed range like $A$2:$D$7, a new item in row 8 is not looked up. The neat fix is to turn the master table into a Table (Ctrl + T) and refer to the table name; the range then grows automatically as rows are added.
Practice exercises (with answers)
Use the price list A1:D7 and the threshold table K1:L6 above. Try first, then check the answers. Retype the tables into Excel or Google Sheets to test for real, as in Figure 4.
Photo: Simon Hattinga Verschure webmarbles / Wikimedia Commons, CC0
- Write a formula that returns the unit price of SP04.
- An order contains SP02 × 3, SP04 × 5 and SP01 × 1. Calculate the total.
- Write a formula that returns the unit of SP05.
- Grade the scores 6.4 and 9.
- What does
=VLOOKUP("SP07",$A$2:$D$7,4,FALSE)return? Change it to show "Code not found". - What does
=VLOOKUP("SP01",$A$2:$D$7,5,FALSE)return, and why?
Answers
=VLOOKUP("SP04",$A$2:$D$7,4,FALSE)→ 27,000.- 3 × 52,000 = 156,000; 5 × 27,000 = 135,000; 1 × 185,000 = 185,000. Total 156,000 + 135,000 + 185,000 = 476,000 VND.
=VLOOKUP("SP05",$A$2:$D$7,3,FALSE)→ "carton".- 6.4 is below 6.5, so threshold 5 applies → Average; 9 equals threshold 9 → Excellent.
- It returns #N/A because SP07 is not in the table. Fix:
=IFERROR(VLOOKUP("SP07",$A$2:$D$7,4,FALSE),"Code not found"). - It returns #REF! because the range A:D has only 4 columns, so there is no column 5.
Self-assessment criteria
- Pass: 6 out of 6 correct; every formula has FALSE (or 0) and a range locked with $; question 2 totals 476,000.
- Pass: you can explain in words why question 4 gives Average and not Good.
- Not yet: you leave the fourth argument empty, or filling down produces #N/A in lower rows and you do not know why.
The next step in the path is PivotTables, charts & dashboards: once the order table has unit prices and line totals thanks to VLOOKUP, you will summarise revenue by month, product and salesperson with a few drag-and-drop moves.
Keep learning step by step: See the 8-step office computing path (Excel, Word) (in Vietnamese) on NAMBIN, from Excel basics to a complete report set and hiring tests.
.jpg){kind=link}