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 contains 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 pre-defined formulae for VLOOKUP which is extensively used in Excel is 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 which is Boolean in nature. A TRUE value indicates approximate match while a FALSE value indicates 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; VLOOKUP needs to be used to join the ‘student’ table and the ‘score’ table.
What are the 10 things analysts should know of VLOOKUP in Excel?
While the usage of VLOOKUP is pretty simplistic & straightforward (as explained in the previous section); there are a few tricks which 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 progresses only towards 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 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 match 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 merge as well as categorize data. The only mandatory aspect here is the presence of a key field which 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 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 allows developers to use wildcard characters as well (‘*’ and ‘?’ are the two wildcard characters which can be used with VLOOKUP)
- VLOOKUP can be merged with IFERROR clause to handle missing data in a more logical manner
- Data type synchronization is a mandate for using VLOOKUP. The value which is looked up, and the destination table which is being referenced should ideally have the same datatype.