In the office area Vlookup is one of the most used MS excel function. And no wonder because there is vast range of possible usage of this simple but powerful function.
Vlookup (abbreviation of vertical lookup) can simply add additional information from a separate list of data. Below is guide with pictures and description and at the bottom of the page a video guide.
Exmaples of Vlookup use:
- Find price for a product from the list of product prices
- Find zip code for a place from the list of zip codes
- Find sport result from last season
- Check whether item is or is not on the list
- Find the number of previous order by the same customer
Do you like sport betting? You have probably tried to make some sort of statistics and tried to place the best bet based upon the past results. Vlookup is a simple solution how you can check how the teams have played in the past. (more…)
Often you get a task to compare a list with another one in order to say which things are common on both. It might be the list of things you can sell compared with received order; list of employees with bank accounts compared with list of people who get extra salary or a list of embargo restricted areas compared with clients addresses. In all the cases vlookup can help to find the right lines you need to review.
Basically there are only 3 main errors with Vlookup formula. They have however several implications and can be even useful in many cases.
- #N/A error – the value is not in the searched list (table_array)
- #REF error – col_index_num is higher than the number of columns in your table array
- which mean that your looking for the information in the xth column which is however not in your list; e.g. fifth column out of three
- #VALUE error – col_index_num is lower than 1
- which mean that your looking for either zeroth or negative column
- Invalid reference – combining later excel version with 2003 (.xls) or older which have 65636 columns only
- happens when your lookup_value is in 2003 or older version of excel .xls and you search through entire columns in later versions 2007, 2010
#REF and #VALUE error are also linked to the fact that you’re searching through the first column of your table array and you can receive the value in any of the columns to the right from this first column. See more in the vlookup basic course.
Sometimes your vlookup returns #N/A error even though you see that the value is there or you can find your value by search (CTRL+F).
Error diagnosis: Vlookup returns #N/A even though you can visually confirm that the value is there and even search (CTRL+F) finds your value
Quick check: Click into the cell where is your look up value or into the first column of table array and check whether there are blank spaces at the end or at the beginning of the text
Quick fix: Use =Trim() function to clear the cells of blank spaces (more…)
Main assumption of vlookup is that your lookup value is in the first column of table array (data inside which you search). Vlookup takes your lookup value and searches the first column from the top to the bottom; whenever it finds a match vlookup returns the value on the same row. However if your value is not in the first column, vlookup doesn’t find anything and returns only #N/A.
Error diagnosis: Vlookup returns #N/A value in all (if you’re unlucky in most) cases
Quick check: Vlookup searches through the first column of table array but your lookup value are in another column
Quick fix: Move your look up values to the first column or move the table array so that your value are in the first column. (more…)