What is the VLOOKUP function?
VLOOKUP is a very useful function that helps you search data quickly, increasing work efficiency
Learn more at Microsoft Support.
Usage
Follow the following example:
Example 1: Accurate detection of color names by color code
In this example, Tuan has 2 tables, the first table is the product table, the second table contains the collation color names to fill in column D in table 1.
These are the cases where we will use the search function to increase productivity and avoid errors instead of doing it manually.
Tuan will apply the VLOOKUP function as follows:
In cell D4, Tuan will use the formula:
=VLOOKUP(C4, $F$3:$G$8, 2, 0)
Tuan will pass the VLOOKUP function 4 parameters:
- C4: Is the color code of the product
- F3:G8: As the area containing the color code and color name, remember to fix the area with the F4 key or the $ character to avoid the formula being skewed when flash fill down the lines below.
- 2: As column Tuan needs to get the value, here is column G - color name.
- 0: you can also enter FALSE, for accurate detection.
After applying, Tuan obtained the result in cell D4 is Black, because the color code A01 corresponds to black in the palette, flash fill down all the remaining lines in the product table to complete.
Example 2: Detect and categorize students' academic performance by scores
Similar to example 1, Tuan needs to fill in the academic capacity corresponding to the score.
Tuan will fill in cell D4, the following formula:
=VLOOKUP(C4, $F$3:$G$7, 2, 1)
The parameters passed are the same as example 1, however, the last parameter [range_lookup], Tuan will enter 1 or TRUE, to search for the closest match to the search value.
The resulting cell D4 is Good, flash fill down the remaining lines to finish.
How to use
Formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Lookup_value: The value to look up.
- Table_array: The area containing the data you need to get.
- Col_index_num: Number of columns of data you want to get, from left to right.
- [Range_lookup]: 0 / FALSE – exact search, 1 / TRUE – relative search.
Notice:
The VLOOKUP function will return the first value, if the collation table has duplicate values.
When you do a relative search, the reference table must be pre-sorted, or you will get an N/A error, or incorrect results.
Above is an article on how to use te function VLOOKUP in Excel.
Wish you success in applying to work or study!
Combining sheets together using VBA
Instructions to combine Excel sheets with the same columns into one using VBA
Create a snow effect with HTML and CSS
Decorate your blog with snow effect with Tuan to welcome the coming Christmas season.
Insert images automatically with VBA
Instructions for inserting images automatically for Excel with VBA, the most detailed and easy to do.
