VLookUp in excel introduces step by step(10 examples), include Reverse lookup,onetomany lookup
The VLookUp in excel is a function for finding a specified value by row; it has at least three parameters, and the fourth parameter is an option to determine the matching option (exact match or approximate match). If it is left out, the default is approximate match. The VLookUp function involves a lot of lookups. If the settings are incorrect, you may return multiple error values.
The VLookUp function is used in a lot of ways. This article will introduce its syntax, basic usage and common methods.The basic usage describes six examples, the common methods introduce three instances. The examples contain multiple returnerror values, and use wildcard questions mark (?) and asterisks (*), and reverse lookups, and approximate matches, and onetomany lookup.
I, Syntax of VLookUp in excel
1, Expression: VLOOKUP (LookUp_Value, Table_Array, Col_Index_Num, [Range_LookUp])
2, Description
(1), Col_Index_Num takes the leftmost column of Table_Array as the first column. For example, if the specified rangeis B2:D8, the column B is the first column; if Col_Index_Num is set to 2, the value of column C is returned.
(2) Range_LookUp is an optional parameter. It has two options, one is True, the other is False, True is an approximate match, False is an exact match, and if Range_LookUp is left out, True is selected by default.
(3) Common return errors
A. If Range_LookUp is set to True (approximate match), the first column of Table_Array needs to be sorted, otherwise the error value may be returned; if Range_LookUp is set to True, LookUp_Value is smaller than the smallest value of the first column in Table_Array, and the error value #N/A will be returned; If Range_LookUp is set to False, if the error value #N/A is returned, there is no lookup value in Table_Array.
B. If Col_Index_Num is greater than the number of columns in Table_Array, the reference error value #REF! will be returned.
C. if Table_Array is less than 1, it will return the value error #VALUE!.
D. If the formula is missing quotes, it usually returns the name error value #NAME?.
(4) When searching for numbers or dates, the first column of Table_Array cannot be set to text, otherwise incorrect values may be returned.
(5) If Range_LookUp is set to False (exact match) and LookUp_Value is text, you can use the wildcard question mark (?) and asterisk (*) in LookUp_Value; the question mark represents any character, and the asterisk indicates one or more characters. If you want to find the question mark or asterisk, you need to add the escape character ~ in front of them, for example, to find the question mark(?), it should be like this ~?.
(6) There must be no spaces, or single and double quotation marks, or nonprinting characters in Table_Array. If there are these characters, they should be removed by function or replacement. Otherwise, unexpected values may be returned.
II, Usage and examples of VLookUp in excel(VlookUp step by step)
(I) Parameter Range_LookUp is left out
1. If you want to find the "sales" of "Pink Shirt". Select cell A11, copy the formula =VLOOKUP("Pink shirt",A2:D10,4,), and paste it in cell A11, press Enter, return to the search result 892; the operation steps are as shown in Figure 1:
Figure 1
2. Formula description:
A. "Pink shirt" is the lookup value in the formula =VLOOKUP("Pink shirt",A2:D10,4,), A2:D10 is Table_Array, and 4 is the return column number (that is the column A is the first column of Table_Array, and the fourth column is returned, that is the value of column D), the formula is left out the fourth parameter Range_LookUp, the default is True (approximate match). The formula means: look for "Pink shirt" in Table_Array B2:E8, and return the value in D8 that is corresponding with A8 after A8 is found in column A, that is, returns 892.
B. If the fourth parameter Range_LookUp is left out, there are both comma (,) and comma (,) can be left out after the third parameter in fourth.
(II) Set the parameter Range_LookUp to True, the first column of Table_Array needs to be sorted alphabetically or numerically, and LookUp_Value is less than the smallest value of the leftmost column of Table_Array will return #N/A
1. Doubleclick cell B13, copy the formula =VLOOKUP(A13,C2:D10,2,TRUE), and paste it in cell B13, press Enter, return the error value #N/A; select C2:C10, and select the "Data" tab, click the "Sort A to Z" icon, open the "sort Warning" window, select "expand the selection", click "Sort", then each line is ordered by "price" "smallest to largest", the value in B13 becomes 981. Select cell A13, enter 5, click B13, then the value in B13 becomes the error value #N/A; the operation steps are as shown in Figure 2:
Figure 2
2. Formula description:
A. Formula =VLOOKUP(A13,C2:D10,2,TRUE) Set the parameter Range_LookUp to True, return the error value #N/A before sort by the "Price", and sorted by the "price" "smallest to largest", can return the correct lookup value 981, indicating that when the parameter Range_LookUp is set to approximate match, the first column of Table_Array needs to be sorted to ensure that the correct lookup value is returned.
B. The first column of Table_Array can be sorted. If the lookup value(5) is smaller than the smallest value(7) of the leftmost column of Table_Array, the error value #N/A is also returned.
(III) Set the parameter Range_LookUp to False to return error value #N/A
1. Doubleclick cell B13, copy the formula =VLOOKUP(A13,C2:D10,2,FALSE), and paste it in cell B13, press Enter, return the error value #N/A; the operation procedure is as shown in Figure 3:
Figure 3
2. When the parameter Range_LookUp is set to False (exact match), Whether to sort the leftmost column of Table_Array, and if no value is found, the error value #N/A is returned.
(IV) The formula is missing quotes to return the name error value #NAME?
1. Doubleclick cell B13, copy the formula =VLOOKUP(Black tshirt,A2:D10,4,FALSE), and paste it in cell B13, press Enter, return the name error value #NAME?; doubleclick B13, to enter double quotation marks the "black tshirt" each side in the formula, press Enter, return to find value 981; operation steps, as shown in Figure 4:
Figure 4
2. If Look_Value is the text, it must be enclosed in double quotation marks, otherwise it returns the name error value #name?.
(V) Use wildcard question mark (?) or asterisk (*) in the LookUp_Value
(1) Use question mark (?) in the LookUp_Value
1. If you look for the "Clothes" property starting with "Pink" and only six characters after "Pink", and look for "Clothes" property that is only question mark(?). Doubleclick cell B13, copy the formula =VLOOKUP(A13,A2:D10,4,), and paste it in cell B13, press Enter, return to the search result 925. Doubleclick cell A13, change the content to ~?, click B13, the value in B13 becomes 490; the operation steps are as shown in Figure 5:
Figure 5
2. Description:
A. "Pink??????" in A13 means start with "pink" and only six characters behind it, it happens to be "pink shirt", clothing property of A6 and A8 also start with "Pink", but there are more than six characters after "Pink", so the conditions are not met.
B. Change the content of A13 to ~?, indicating that you want to find the question mark, just return ? corresponding sales 490.
(2) Use asterisk (*) in the Lookup_Value
1. If you look for "Clothes" property begin with "pink", or end with "tshirt", or begin with "white" and end with "shirt". Doubleclick cell B13, copy the formula =VLOOKUP("Pink*", A2:D10,4,), and paste it in cell B13, press Enter, return to the search result 329; doubleclick B13, change the Lookup_Valu "Pink*" to "*tshirt", press Enter, return to find result 981; doubleclick B13, change the Lookup_Value "*tshirt" to "white*shirt", press Enter, return to the search result 685; the operation steps, as shown in Figure 6:
Figure 6
2. Description:
Formula = VLOOKUP ("Pink*", B2:E8,4,) "Pink*" means finding clothing starting with "Pink", *Tshirt" means finding clothing that starts with any character and ends with "Tshirt" , white * shirt "represents the look for clothing that begins with "white" and ends with "shirt."
(VI) Parameter Table_Array has spaces before and after
1. If you want to find the "green Tshirt" in A11. Doubleclick cell B13, copy the formula =VLOOKUP(A13,A2:D10,4,), and paste it in cell B13, press Enter, return the error value #N/A; doubleclick B13 and change the formula to =VLOOKUP(A13,TRIM(A2:D10),4,), press Ctrl + Shift + Enter to return to the search value 490; the operation steps are as shown in Figure 7:
Figure 7
2. Formula description:
A. The contents of A7 are "Green tshirt", but the formula =VLOOKUP(A13,A2:D10,4,) returns the error value #N/A, and the space before and after are removed after add the function Trim, and the correct value can be returned, indicating that there are space in A7.
B. =VLOOKUP(A13,TRIM(A2:D10),4,) is an array formula, so you need to press Ctrl + Shift + Enter, because A2:D10 returns all the values in A2:D10 as an array, then use Trim function removes the spaces one by one.
III, VLookUp in excel is used in common examples(VlookUp step by step)
(I) Approximate matching
1. If you want to find the student's Grade that based on the average score. Select cell G9, enter =a5, press Enter, return to "Bulen"; doubleclick H9, copy formula =VLOOKUP(E5,G3:H6,2), and paste it in cell G9, press Enter, return to Bulen's Grade B; process steps, as shown in Figure 8:
Figure 8
2. Formula description:
A. Formula =VLOOKUP(E5,G3:H6,2) means to find the Bulen's average score in G3:H6(Grade Table), if found, return the corresponding Grade, since the number in E5 is 81.3, this value is not available in the Grade Table, so an approximation of the 80 to 89 segment can only be selected with an approximate match (ie, the parameter Range_LookUp is left out or set to True).
B. Tip: The scores in the Grade Table must be sorted in ascending order, otherwise incorrect values may be returned.
(II) Reverse lookup(VLookUp + Choose)
1. If you want to find the corresponding "Product code" according to "Clothes". Doubleclick cell C13, copy the formula =VLOOKUP(B13,CHOOSE({2,1},A2:A10,B2:B10),2), and paste it in cell B13, press Enter, return to "Product code" property WS581; Operation procedure As shown in Figure 9:
Figure 9
2. The formula =VLOOKUP(B13,CHOOSE({2,1},A2:A10,B2:B10),2) Description:
A. {2,1} are the index_num that are an array in the Choose function to specify which value to return. A2:A10 and B2:B10 are the return values, 2 in the array represents the value in A2:A10, and 1 in the array represents the value B2:B10. When executed, first take 2 out of the array, then take B2 from B2:B10(ie "Pink long sleeve shirt") and take A2 out of A2:A10(ie "WS580"), Finally, these two elements are grouped into the first element of array, namely "Pink long sleeve shirt", "WS560"; the second time B3:B10 is taken out of B3(ie "Red tshirt") and Take A3(ie "WS582") from A2:A10, form the second element of the array "Red tshirt", "WS582"; Finally return the array {"Pink long sleeve shirt", "WS560"; "Red tshirt", "WS582"; "White cotton tshirt", "WS580"; "White shirt", "NS832"; "Green tshirt", "WS585"; "Pink shortsleeved shirt", "WS561"; "Black tshirt", "NS286"; "Black tshirt", "WS584"; "Pink shirt", "WS581"}, so put the "Clothes" property in front of the "Product code" property.
B. the formula becomes =VLOOKUP(B13,{"Pink long sleeve shirt", "WS560"; "Red tshirt", "WS582"; "White cotton tshirt", "WS580"; "White shirt", "NS832"; "Green tshirt", "WS585"; "Pink shortsleeved shirt", "WS561"; "Black tshirt", "NS286"; "Black tshirt", "WS584"; "Pink shirt", "WS581"},2), after finding the A11 (black Tshirt), return to the second column, which happens to be WS581.
(III) onetomany lookup
1. If you want to find the First Names of all employees of the designated "department"(Sales department). Doubleclick cell F2, enter "Sales department", doubleclick A2, copy the formula =(C2=$F$2) + A1, and paste it in cell A2, press Enter, return to 0; Select A2 again, move the mouse to the cell fill handle on the lower right corner of A2, after the mouse becomes the black plus sign (+), doubleclick, A3 to A8 are all filled with the corresponding numbers. Doubleclick G2, copy the formula =IFERROR(VLOOKUP(ROW(A1),A1:C$8,2,0),""), and paste it in cell G2, press Enter, return to find the result is "Jaik", select G2, move the mouse to the cell fill handle pn the lower right corner of G2, hold down the left button, drag down, drag until the last row, then return to all employees of the "Sales department". Then doubleclick C2, Select "Finance", doubleclick G2 , change "sales department" to "Finance", press Enter, return to the names of all employees of the "Finance department"; the process steps, as shown in Figure 10:
Figure 10
2. Formula description:
(1)=(C2=$F$2)+A1
A. Formula = (C2 = $ F $ 2) + A1 is used to increment the number of employees belonging to the same department, for example, three employees belonging to the "Sales Department" are numbered 1, 2, 3, belonging to the "Finance department" Both employees are numbered 0 and 2.
B. C2 means that the column and the row are relative references. When drag down, it will change to C3, C4, ...; when dragged to the right, it will become D2, D3, .... $F$2 means that the column and the row are absolute references. When you drag down or to the right, F2 does not change.
C. C2=$F$2 is a condition, that is, if C2 is equal to F2, it will return True, otherwise it returns False, and content of C2 is "Finance", and content of F2 is "Sales department", they are not equal, so it returns True. Then the formula =(C2=$F$2)+A1 becomes =FALSE+A1. When calculating, FALSE is converted to 0, A1 is empty, and 0 is returned by default, so =TRUE+A1 returns 0.
D. Drag down to A3, C2 becomes C3, A1 becomes A2, so the formula of A3 is =(C3=$F$2)+A2; the value of C3 is "Sales Department", equal to F2, so return True, the value of A2 is 0, so the formula becomes =1+0. Since True is converted to 1 when calculated, =1+0 returns 1.
(2) =IFERROR(VLOOKUP(ROW(A1), A1:C$8,2,0),"")
A. ROW(A1) returns the row number 1 of A1; A1:C$8 means the cell range that is from A1 to C8, when drag down, A1 will become A2, A3, ..., C$8 will not change; When the formula is in G2, A1:C$8 is A1:C8; when the formula is in G3, A1:C$8 is A2:C8, and so on.
B. When the formula is in G2, VLOOKUP(ROW(A1),A1:C$8,2,0) becomes VLOOKUP(1,A1:C8,2,0), that is, find 1 in A1:C8, and find 1 in A3, then return to the corresponding column "Jaik"; the fourth parameter 0 means an approximate match.
C. When the formula is in G3, VLOOKUP(ROW(A1), A1:C$8,2,0) becomes VLOOKUP(ROW(A2), A2:C$8,2,0), ie VLOOKUP(2,A2:C8 ,2,0), the lookup value becomes 2 and A1 is excluded from the search range, 2 is found in A5, and the corresponding "Chailiste" is returned in the second column; when the formula is in G4, the lookup value becomes 3, and A2 is excluded from the search range. Others and so on.
D. IFERROR function is used to return the error value. If VLOOKUP(ROW(A1),A1:C$8,2,0) returns an error value, it returnsnull, otherwise it returns the value of VLOOKUP(ROW(A1),A1:C$8 ,2,0) return value.
In addition to the use of the above VLookUp function, the VLookUp function can also use the If function to combine multiple conditional lookups, find duplicates, and find in combination with the Match function. These will be introduced in subsequent articles.

Related Reading
 Excel Len and LenN function usage(7 examples, with I
 Excel Search function and SearchB usage(13 examples,
 How to move rows,columns,cells,table in excel(there
 Excel CountA and CountBlank function usage examples(
 Excel lookup function usage(Multiple conditional and
 How to use Replace function in excel(9 examples, wit
 Excel AverageIfs fuction usage(7 examples, include m
 8 examples of Excel Match function, include it and S
 How to use offset function in excel, include it and
 How to use Excel rank function(11 examples, with Ran
 How to use Excel frequency function(6 examples, with
 Excel SumProduct function(multiple criteria, with if