VLOOKUP in Excel – Complete Guide with Real-World Examples | Excel VLOOKUP Formula Tutorial, by Ankit Sen on YouTube. 8 min 48 s.
- Video length
- 8 min 48 s
- Time to do it
- 15 minutes
- Difficulty
- Easy
- Steps
- 8
What you need
Steps
Put the value you want to find in one cell, and make sure that same kind of value sits in the first column of your data table. VLOOKUP only looks to the right.
In an empty cell, type =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). Each part gets replaced with your own cell or range.
Select your lookup cell, type a comma, then highlight the whole table including the first column. Press F4 to lock the range, for example $A$2:$D$100.
Type the column number you want back, counting the first column of the table as 1. For a price stored in column 3, enter 3.
Close with FALSE or 0 for an exact match. Use TRUE or 1 only when the first column is sorted, such as tax bands or grade limits.
Wrap the formula in IFERROR, for example =IFERROR(VLOOKUP(A2,$A$2:$D$100,3,FALSE),"Not found"), so missing values show a clean message instead of #N/A.
To return several columns, copy the formula across and use COLUMN() or MATCH() for the column number instead of typing a new number each time.
To look up from another sheet, put the sheet name and an exclamation mark before the range, like Sales!$A$2:$D$100.
Tips and common mistakes
If VLOOKUP returns #N/A, check for numbers stored as text and extra spaces; TRIM and VALUE fix most of these cases.
The lookup value must be in the first column of the range, because VLOOKUP cannot return values to its left.
Use FALSE or 0 unless your data is sorted, since approximate match on unsorted data gives wrong results.
Lock the table range with $ signs so copying the formula down does not shift it.
Questions
Why does my VLOOKUP return #N/A when the value is clearly there?
The usual causes are numbers stored as text, extra spaces, or a range that does not start at the lookup column. Clean the data and check the range.
What is the difference between TRUE and FALSE in VLOOKUP?
FALSE, or 0, finds an exact match and is the safe default. TRUE, or 1, finds the closest match and needs the first column sorted.
Can VLOOKUP look to the left?
No. It only returns columns to the right of the lookup column. Use XLOOKUP or INDEX and MATCH when you need a left lookup.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
