Vlookup in Excel (function explained, 4 examples, certain words, text, asterisk, Approximate match)
Vlookup is one of the commonly used functions in Excel, it is used to find another value corresponding to a specified value, especially when there are many records of table, we can quickly find the value we want to find.
Chapters:
0:00 How to use Vlookup in Excel?
0:11 Parameter explanation(Vlookup function explained)
0:38 Vlookup example
1:24 Vlookup certain words (Vlookup certain text, Vlookup asterisk)
2:04 Approximate match in Vlookup (Approximate match in Excel)
2:50 Vlookup row number
Description:
Vlookup in Excel (function explained, 4 examples, certain words, text, asterisk, Approximate match)
One. How to use Vlookup in Excel?
(1) The role of vlookup
Vlookup is used to find another value that corresponds to a specified value. For example: find the price of a certain product, a certain subject's grade of a classmate, etc.
(2) Vlookup function expression:
VLOOKUP (LookUp_Value, Table_Array, Col_Index_Num, [Range_LookUp])
Parameter explanation(Vlookup function explained):
1. LookUp_Value: we can refer to the value of the cell, such as B6; It can also be entered directly, such as "red T-shirt".
2. Table_Array: Used to specify the search range, such as A2:D10.
3. Col_Index_Num: used to specify which column the return value is in, and the column number must be calculated from the specified range; For example, if the range is B2:E8, column B is the first column, and if the return value is in column number 3, the value is returned from column D.
4. Range_LookUp; Approximate matches are indicated by 1 or True; It is optional, we can fill in or not; If not specified, the default value is approximate matching.
(3) Vlookup example
1. Suppose we want to find the price of a t-shirt (Black T-Shirt) from a clothing sales table. Select B13, enter =b6, press Enter on the keyboard, then refer to the value of b6, i.e. "Black T-Shirt"; Double-click C13, copy and paste the formula =VLOOKUP(B6,B2:F10,2,0) to C13, press Enter on your keyboard to find the price of "Black T-Shirt".
2. Formula description:
B5 is the value to be found, B2:H9 is the lookup range,
5 is the column number where the return value is located (5 is column 5 starting with column B [i.e. column F]),
0 indicates an exact match;
The formula means to find the price of B5 (i.e. "Black T-shirt") from the range B2:H9.
Two. Examples of extended usage of the Vlookup function
(1) Vlookup certain words (Vlookup certain text in a cell, Vlookup certain characters in cell, Vlookup asterisk)
1. If we want to find out if there is a garment ending in "T-Shirt" in the clothing sales table, if so, display the price. Select cell C13 and copy and paste the formula =VLOOKUP("*" & "T-Shirt",B2:F10,2,0) to cell C13.
2. Press Enter on the keyboard, the result of formula execution is 8.8, which happens to be the price of B2 "White T-Shirt", indicating that the formula is correct.
3. Formula explanation:
means any character,
& is a connecting symbol,
"*" & "T-Shirt" means to find clothing that starts with any character and ends with "T-Shirt".
4. If you find clothing that starts with a specified character (such as "Pink"), the formula can be written like this: =VLOOKUP("Pink" & "*", B2:F10,2,0).
If you find clothing that contains a word, the formula can be written like this: =VLOOKUP("*" & "short" & "*", B2:F10,2,0).
(2) Approximate match in Vlookup (Approximate match in Excel)
1. Suppose we find the Grade of a given student according to the Grade Table. Enter =A3 in cell G3 and Press Enter on the keyboard, then the value of A3 is referenced; Enter the formula =VLOOKUP(E3,H:I,2), Press Enter on the keyboard, then find Tomular's Grade "B".
Note: The Grade Table must be sorted in "ascending order", otherwise an approximate match cannot be achieved, and the formula will return an incorrect result.
2. Formula description
The Range in the formula =VLOOKUP(I3,L:M,2) that references the Grade Table is sufficient only with the column number (that is, L:M), and does not need rows, otherwise the correct result will not be returned. If the fourth parameter is omitted from the formula, it is "approximately matched" with the default value.
(3) Vlookup row number (Find prices for all products in a specified classification)
Suppose we want to find all the prices for "Shirts" in "Classification2" in the clothing table. First enter "Classification2" and "Price" in cells I1 and J1 respectively, and then enter the "Shirt" we want to find in cell I2; Copy and paste the formula =(D2=$H$2)+A1 to cell A2, press Enter on the keyboard, move the pointor to the fill handle of cell in the lower right corner of A2, drag down, and generate a set of sequences; Copy and paste the formula =IFERROR(VLOOKUP(ROW(A1),A:E,5,0),"") to cell I2, press Enter on the keyboard, then return the price of the first "Classification2" belonging to "shirts", the same method drags down, returns all prices belonging to "shirts".
Watch video Vlookup in Excel (function explained, 4 examples, certain words, text, asterisk, Approximate match) online without registration, duration hours minute second in high quality. This video was added by user Loinsure 16 October 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 115 once and liked it 1 people.