Mastering the use of functions will save us a lot of time compared to manual calculations without using functions. These functions are very diverse and cover many fields, most notably the Vlookup function in Excel, which allows users to search and return corresponding data by column.
The VLOOKUP function in Excel can be used in almost all versions, from old to new, as it is one of the basic formulas in Excel. However, not everyone knows how to use the VLOOKUP function to look up data in a problem.
Formulas and usage of the Vlookup function in Excel
I. What is the Vlookup function in Excel?
VLOOKUP is a function in Excel that searches for values by column and returns a value vertically. It's a fairly basic and common Excel function for users who perform statistics and search for data precisely by column.
II. Vlookup function formula in Excel
- Lookup_value: The value to search for.
- Table_array: The table range to search within.
- Col_index_num: The column number from the table to retrieve data, counting from right to left.
- Range_lookup: The search range (TRUE or FALSE).
+ If Range_lookup = 1 (TRUE) : Approximate search.
+ If Range_lookup = 0 (FALSE) : Exact search.
+ If the above two cases are omitted, Excel will automatically assume Range_lookup = 1 by default.
III. Instructions on using the Vlookup function in Excel
To understand how to use the VLOOKUP function in Excel in a simple way, let's look at a few specific examples of relative and exact lookups.
1. How to use the Vlookup function in Excel for relative lookup
- Example: Taimienphi has a score table as shown below. You need to classify the scores based on the requirements of table 2 .
- Enter the formula =VLOOKUP(D4,$B$11:$C$15,2,1) into cell E4 in the "Rating" column . Excel will then retrieve the score from column D4 and search within Table 2. The result returned is "Excellent" in E4 . + The $ symbol: Used to fix the rows and columns of Table 2 when the user copies the formula to another cell. + 2: The column number. + Range_lookup = 1 (TRUE): Relatively searches for the closest result.
- You can successfully assign scores to other members by simply copying the formula to other cells or using Flash Fill .
2. Using the Vlookup function in Excel for exact matching
- Below is an example of an employee allowance table . Use the Vlookup function to determine the allowance level for each person according to their corresponding position between Table 1 and Table 2.
- First, enter the formula =VLOOKUP(D4,$B$11:$C$15,2,0) , with Range_lookup = 0 (FALSE) for an exact match. The first allowance result will be displayed as shown below.
- As above, you just need to copy the formula to each Allowance cell or use Flash Fill to complete the calculation of employee allowances.
IV. Limitations of the Vlookup function
- Only supports left-to-right references.
- Only works effectively with unique values.
- Slows down spreadsheet responsiveness.
- The column numbering for references is fixed, making copying to other cells difficult.
- When no content is entered into a formula, Excel defaults to an approximate match.
In addition to using the VLOOKUP function in Excel, you can also combine HLOOKUP and VLOOKUP to search and calculate data, saving you even more calculation time and making your work more concise and simpler.