Hello all. Let’s look at one of most commonly used functions in excel – Vlookup. Vlookup function lookup for a specified data in a selected table / range and returns the match.
=vlookup(lookup_value, table_array, col_index_num, [range_lookup])
There are certain conditions while using vlookup:
- Lookup value should always be the first column of the table array
- Data has to be organized vertically
- Vlookup will only look from left to right (from column 1 to column n)
- If you are using approximate match (more on what this towards the end of this section, data has to be sorted in ascending order
Let’s look at few examples to understand vlookup better. In the below example, we have a master data range (A1:F7). We also have the 2nd set of data range (I1:I7) where we need to find the type of assets each employee has got.
Let’s use vlookup in J2. The formula would be
Here, the function takes the lookup value has I2 (emp#1001), searches for the value in table range A1:F7, checks for the 5th column (this where we have the asset type) and returns the exact match (0 or FALSE for exact match and 1 or TRUE for approximate match). Here is the output
Please be mindful that the vlookup function will search from left to right and also returns the value of the column number from start of the table reference – so please be careful while giving the column_index_no. In the same example if we would give the column number as 4 instead of 5, the function would return the Date of Joining instead of asset type.
Here is the example where we have given different column numbers and the corresponding results:
Now, let’s take Name as the lookup value instead of Emp ID and apply the same formula:
We get “#N/A” – why? Remember, the lookup value has to be the first column in the table. Here, name is the lookup value – but the first column in the table is still Emp ID. To get some value instead of error, we need to change the table reference as B1:F7:
Note, the column number for Asset type has changed to 4 as we are starting from B.
Vlookup would return “#N/A” error if there is no match or if the values are mis-spelled in the table. We can trap this error and return custom message by using IFERROR or IFNA functions.
We can use wildcards in vlookup to search data. In the below example, we have given the name as Rah* – * denotes wildcard. If you enter without * the function would return NA error as it doesnt have a match in the table
When you are using Vlookup be absolutely careful in inserting a new column in the table range. This action will break the formula as the column number within the function is static.
Finally, let’s look at the two matching options Vlookup gives to us. By default, Vlookup returns the exact match. However, we can force the function to provide either exact or appropriate match. “0” or “FALSE” in place of [range lookup] would return exact and “1” or “TRUE” will return approximate.
As we have seen the exact match, let’s look at different dataset for approximate match – remember for approximate match we need sorted data.
In the below example, we have sales data for each employee. We also have the company’s commission policy / standards to calculate the commission %. If an employee does a sales between 0 to 500, then commission would be 0%, >500 but <1000, then 2% and so on
We need to calculate each employee’s commission % based on their sales number and the policy. Let’s do a vlookup – =VLOOKUP(B2,$H$1:$I$8,2,1). We are taking the sales number (B2) as the lookup value, the commission policy as the table range, we need to return the 2nd column. This time we have given “1” as the range lookup for the approximate match.
Here, though the table may not have “3749” to lookup but the approximate match would trigger the function to step back to the previous available number – in this case 3500. 3500 has 6 as the value and the same is returned. As the function is triggered to go one step back to the previous available value – we ensure the data is sorted.
To re-iterate, Vlookup is only for values vertically and lookup being the first column of the table. If you have values horizontally, you need to check Hlookup and if you don’t have tables that may not have the lookup value as first column always, check out Xlookup. Xlookup is currently available in Excel 365
I hope you like this tutorial. As always, please provide your valuable feedback. Thanks for reading.