Use a VLOOKUP (Lookup Left to Right)
View/download Topic Example Workbook
Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.
If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.
Overview
The VLOOKUP function looks up a value in the first column of a range and returns the value in the same row for a column further to the right.
The VLOOKUP function is probably one of the most commonly used functions in Excel, almost as a benchmark of standard Excel proficiency, and largely for good reason-performing a lookup for values in one list of data against values in another is a fairly intuitive way of understanding how your datasets relate to each other. Here we will look at how to do this using an exact VLOOKUP (looking up values exactly as they are).
Once you’ve mastered this the next stage is knowing when not to use an exact VLOOKUP- for example using INDEX/MATCH when you want more flexibility for lookups, using an Inexact Lookup when you want to look up against a set of categories/bands, and using a helper column to lookup across multiple columns or an aggregate function such as SUMIF/SUMIFS when the range contains multiple occurrences of the value being looked up.
Review the syntax of a VLOOKUP function
The Syntax of the VLOOKUP function is =VLOOKUP(Lookup_Value,Table_Array, Col_index_Num,[Range_Lookup])
In the Topic Example workbook, say we want to use a VLOOKUP Function to determine how many products James sold – we want to lookup “James” in Column A and return the corresponding value in the same row in Column C.
The table below describes each element of the syntax and its applicable value in the example workbook.
| Element | Description | Value in Topic Example Workbook |
| Lookup Value | The value which you want to find in the first column of the table array below |
“James” |
| Table_Array | The Columns (or range) which you want to look at – starting with the column you want to find the lookup value in (in this case Column A) and including the column you want to return a value from (in this case Column C) |
=A:C |
| Col_Index_Num | How many columns away from the first Column (e.g. Column A) the column you want to return values from is (e.g. Column C). In this case Column C is the third column in the range A:C so the Col_Index_Num is 3. |
3 |
| Range_Lookup | A TRUE or FALSE value that indicates whether you want to find an exact match (FALSE) or a match within a range (TRUE). In this kind of scenario, we are doing an exact lookup so will always use FALSE as the Range_Lookup. |
FALSE |
Therefore the completed VLOOKUP Formula is =VLOOKUP(“James”, A:C, 3, FALSE). If you type this into cell F2 above you will obtain a value of 200, indicating that James sold 200 units. If the salesperson name is located in cell E6 then instead of hard-coding the name you can refer to this cell as the lookup value, making the formula =VLOOKUP(E6, A:C, 3, FALSE).
Rather than referencing entire columns as the table array, you can instead reference a range of cells (e.g. A2:C5) though if you do this ensure that the range is entered as an absolute reference (e.g. as $A$2:$C$5) so that when the VLOOKUP formula is dragged down, the table array remains fixed. It is also important to remember that the Col_Index_Num is the column number of the lookup column relative to the first column in the table array, not the column number of the spreadsheet overall.
If the VLOOKUP formula can’t find the value being looked up in the range, it will return a #N/A error. It is possible to error trap these to replace the error value with something more intuitive (such as 0 or “value not found”). If the VLOOKUP formula returns #N/A errors for values which you are sure exists in the range, this may be due to the formatting of the cells (e.g. numbers stored as text), or else because of invisible spaces in the lookup value/range values which can be resolved by use of the TRIM function.
Try it Out
In the Topic Example Workbook, write a VLOOKUP formula in cell F2 to find the quantity sold by James (hard-coding the name in the formula).
In cells F6:F10, write VLOOKUP formulas to find the corresponding quantity sold by each sales person in cells E6:E10 (Refer to the cells in Column E rather than hard-coding the names in the formula).
A demonstration of the first part of this is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!
Answers
Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:
If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.
Considerations
The following are some things to think about when using a VLOOKUP Formula:
- How can I Lookup right to left instead? (e.g. find which salesperson sold a particular quantity?)
- See USING AN INDEX/MATCH (Lookup Either Way). In fact even for looking up left to right it’s better to use an INDEX/MATCH rather than a VLOOKUP – see the considerations section of the INDEX/MATCH article.
- What happens when there is more than one occurrence of the lookup value in the range (e.g. if there were two occurrences of “James” in Column A)
- VLOOKUP will only return the first Instance
- To tell if there is more than one occurrence use a COUNTIF function
- To deal with multiple occurrences you’ll need to make the value unique by combining the values in multiple columns– see the Lookup against multiple columns topic
- Using an Array Formula, It is also possible to do a lookup and return multiple values (Advanced)
- How can I make the VLOOKUP Formula easier to understand?
- Use named ranges instead to refer to cells/columns
Links and other resources
YouTube Video – Microsoft Excel VLOOKUP Tutorial for Beginners
Related Articles
