VLOOKUP in 6 Minutes: Beginner Tutorial (Excel & Google Sheets), by ReconScribe on YouTube. 6 min 43 s.
- Video length
- 6 min 43 s
- Time to do it
- 10 minutes
- Difficulty
- Easy
- Steps
- 9
What you need
Steps
Open your sheet and check that the column you search in, such as product code, is the first column of your data table. VLOOKUP only looks to the right.
Click an empty cell and type =VLOOKUP( to start the formula.
Click the cell holding the value you want to find, for example H2, then type a comma.
Select the whole table range, then type a comma. Count the index from the left edge of that range, not from the column letters on screen, so the second column of the range is 2.
Type a comma, then FALSE, then a closing bracket. FALSE forces an exact match, which is what you want almost every time.
Before copying, click inside the range in the formula bar and press F4 to add dollar signs, or Command and T on a Mac. The range is now locked.
Press Enter, then drag the fill handle down. The locked range stays fixed while the lookup cell shifts down one row at a time.
If you see #N/A, check the code really exists in the first column and that the locked range covers every row of data.
To hide errors, wrap the formula as =IFNA(VLOOKUP(H2,$A$2:$E$11,2,FALSE),"Not found") so a missing code shows text instead of an error.
Tips and common mistakes
Type the range, press F4, then carry on. Locking after you copy is the mistake that breaks most spreadsheets.
In Google Sheets the four arguments are called search_key, range, index and is_sorted, but they work the same way.
VLOOKUP cannot look left, so move your matching column to the left of the table or use XLOOKUP instead.
Test one row with a code you know before filling the formula down a whole column.
Questions
Why does my VLOOKUP return #N/A when the value is clearly there?
Usually the two cells do not match exactly. One may have a trailing space, or be stored as text instead of a number. Check the first column of your range and trim stray spaces.
What does the FALSE at the end actually do?
It tells the formula to accept only an exact match. Leaving it out or typing TRUE lets Excel guess at the nearest value, which gives wrong answers in most lists.
Does the same formula work in Google Sheets?
Yes. Paste it in and Google Sheets converts it, or type it with the argument names search_key, range, index and is_sorted.
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.
