Excel is widely used in many offices for maintaining employee data, company accounts, and other important details in a systematic order. But, finding particular information from a large data-set may sometimes be difficult for an employee. It is therefore advised to use VLOOKUP function in excel to filter large data arranged in different tables by selecting an appropriate category based on the given condition.
If you’re finding it difficult to use or access VLOOKUP function in Excel, here’s a quick guide that could make your task easier. Follow the below mentioned steps if you’re not familiar with the Excel’s VLOOKUP functionality:
1. To start with, a complete database must be available so that the required data can be sorted out well. Below is an example of a table with unique categories:
2. Every database contains an exceptional identifier. In the above table, “Item code” is a distinctive identifier.
3. To use Excel’s VLOOKUP functionality, the column header must be different from the other columns and it must be the first column of the database.
4. To use the VLOOKUP function, the following information must be available:
- Lookup_value: The value which you want to look for
- Table_array: The cells in the table which form the complete table
- Column_index: The column number to retrieve the information or result
- Range_lookup: TRUE for approximate match or FALSE for an exact match
5. Add an extra column which pulls out the information from the LOOKUP table.
6. Column E is added as an example above.
7. Place the cursor on the first blank cell in the column. In the image above, it is E2 cell.
8. Select Function from the insert menu. In the search for a function text box, type “VLOOKUP” and click on go.
9. Select VLOOKUP from the list and click on OK.
10. After clicking OK, the Function Arguments dialog box appears which allows you to define the values.
11. In the LOOKUP value, write D2 which represents in stock to look up the value as 5.
12. In the table_array, select the ranges from where the data must be used.
13. Things to know while using a table array:
- On the left hand side, the column which has to be referred must always be present
- Duplicate values must not be present.
- The cell reference need not be changed for the lookup table while dragging and implementing the same formula for various cells
14. In the column_index, add the column value which has to be used.
15. In the range_lookup, if exact match is required then add False else choose True. Based on the user’s requirement, the range_lookup value can be chosen.
There are numerous ways of using Excel’s VLOOKUP functionality and it depends on your individual requirements to use the same. The above example is the simplest way you can retrieve data from a large dataset. Through VLOOKUP function, users can look for any information present in the database and can also merge the data from different tables and use them collectively.
In case you have any queries about the above, please leave us a comment below.