VLOOKUP is one of the most frequently used functions in Microsoft Excel and is predominantly used to retrieve data from other data sources corresponding to a particular key column in another data set.
Generally, Data Scientists and analysts work on large datasets which contain multiple attributes and fields – all of which cannot be accommodated into a single tabular structure.
Therefore, there are instances wherein multiple fields corresponding to a single record are placed in different tabular structures or data sources.
However, while analyzing them; there may be a need to combine the related fields corresponding to a single record; all of which may not be there in a single source. This is where VLOOKUP is used.
What is VLOOKUP in Excel? And how is it used?
The predefined formula for the VLOOKUP function in Excel are shown below for reference –
VLOOKUP (value, table, column index, [range lookup])
In this formula:
- Value – this is the mapping field i.e. Excel would search this value in the first column of the referenced destination table
- Table – Destination table from where the mapping would be done to retrieve the related data
- Column index – Refers to the column number (relative referencing considering the first column as 1) from where the matching value needs to be extracted
- Range lookup – This is an optional field that is Boolean in nature. A TRUE value indicates an approximate match while a FALSE value indicates an exact match
Now, for example, assume a ‘student’ table with roll number, name & standard. And, there is another ‘score’ table which has all subject’s marks scored by the student. So, if the analyst wishes to get the name of the student scoring the highest mark; the VLOOKUP function in Excel needs to be used to join the ‘student’ table and the ‘score’ table.
What are the 10 things Analysts should know about VLOOKUP in Excel?
While the usage of VLOOKUP function in Excel is pretty simplistic & straightforward (as explained in the previous section); there are a few tricks that all Excel users should know to better use this functionality –
- The value in a VLOOKUP function has to be mapped to the first column of the referenced table, and all subsequent columns can be accessed using respective column numbers (starting from 1). This means that the VLOOKUP function in Excel progresses only towards the right. It would not work if the value needs to be mapped to a center field in the destination table.
- In a VLOOKUP function, the destination column reference has to be numeric in nature; it cannot be an alphabet. For example – if column ‘A’ is the first column and the corresponding data from column ‘D’ needs to be extracted – then the column reference has to be 4 and not ‘D’.
- As already mentioned in the prior section, VLOOKUP uses both exact as well as approximate matches as well. Bu default, it does an approximate match and returns N/A in case there is no matching data corresponding to the value entered
- VLOOKUP can be used to both merges as well as categorize data. The only mandatory aspect here is the presence of a key field that can be used to reference data from two or more different tables
- VLOOKUP can be made portable and enable reusability by including absolute referencing. In the case of relative referencing, the copy-paste of formulae may not work everywhere
- VLOOKUP allows named ranges to be used as well – in case the same range is being used in multiple VLOOKUP formulae
- Excel developers should be a bit careful in hardcoding column references as it would not automatically adjust on inserting or deleting columns
- VLOOKUP function in Excel allows developers to use wildcard characters as well (‘*’ and ‘?’ are the two wildcard characters that can be used with VLOOKUP)
- VLOOKUP can be merged with the IFERROR clause to handle missing data in a more logical manner
- Data type synchronization is a mandate for using VLOOKUP in Excel. The value which is looked up, and the destination table which is being referenced should ideally have the same datatype.