Imagine having a spreadsheet with thousands of entries. Also imagine needing to find a number for one of those entries. Maybe a phone number from a list of employees or a price for a specific item. This is a common problem faced by many Excel users around the globe. However, using Excel’s various lookup functions, this task becomes easy. By the end of reading this, you will be able to use several of these functions to find any information you need, no matter how many entries have to be sorted through to find it.
The cornerstone on searching through Excel is the VLOOKUP function. The VLOOKUP function looks like this:
=VLOOKUP(Lookup_Value, Table_Array, Col_Index_Num, [Range_Lookup])
Using this command, you can sort through most documents with ease. Lookup_Value is the item that you will be searching for. Table_Array is simply the relevant data to search through. Col_Index_Num is the column with the data you would like to retrieve. Range_Lookup is an optional argument to switch between Exact matching and Approximate matching. A sample of the VLOOKUP function is below.
The Col_Index_Num in the above example is 2 because the information to be searched through is in the second column of the selected table of data. Using 0 for the Range_Lookup performs an Exact match. When input, the formula returns the correct item number, shown in the following image.
The limitation of Exact matching lies in its inability to compromise on results. When searching for an item not in the list, the function output is #N/A. This would be caused, for example, by searching for Item3. Leaving out the space causes the Exact match not to find the entry. One way to correct for this is through Data Validation.
First, click on the cell you would like to validate data on, your Lookup_Value reference. In the above example, this would be cell A3. Then select Data and click Data Validation. A dialog box like the one pictured below will pop up. For this example, the List option would work well.
Select the items contained in the list, for this example cells A6 through A15, and the cell will be formatted to only take items contained in the list, as shown below.
The IFERROR function is another way to deal with the problem of Exact matching. The IFERROR function looks like this:
The Value argument is simply what will be checked for an error. The Value_If_Error argument is what will be displayed if there is an error. This can be a simple message, or use “” to display nothing. This function, used along with the VLOOKUP, such as in the following image, can be used to send a custom message to the user in the case of searching for an unlisted item.
The function will return the usual number if the item is found. However, if the item is not found, the function will return the error message specified, as in the following image.
Lastly, we will look at the Approximate option for the Range_Lookup argument. First, it is important to note that in order for this option to work, the table must be sorted. Approximate is the default value for this function. This means leaving the argument out altogether is fine, or it can be explicitly stated by entering a 1 for the value.
When searching for an approximate match, it will look for the highest number without going higher than the Lookup_Value. An example of a search using Approximate match can be seen below.
In this case, the highest value under the Lookup_Value, 1200, is 1000. Therefore, the function returns 51, the number associated with 1000.
It is worth pointing out that if the Lookup_Value is higher than the highest value in the list, the VLOOKUP function will retrieve the last number in the list. For example, if in the above spreadsheet, 30000 was used as the Lookup_Value, the formula would return 884, the number associated with 25000. Since there are no numbers larger than 25000, it returns the largest.
While there are other options, and even entire functions, that can add to the value of the VLOOKUP function, this formula alone makes searching through impossibly large amounts of data a breeze. Using both Exact and Approximate matching, nearly any type of data can be easily sorted through. Data Validation and the IFERROR function fit seamlessly with VLOOKUP, adding even more versatility to this powerful function. These skills will make data location in Excel both quick and simple.