If you want to say which function is the most common in excel, the vlookup function is definitely one of them, today we will summarize the 12 common uses of this function, these usages are definitely commonly used operations in work, I believe it will be helpful to you.
vlookup($b18,$c$7:$h$15,column(b1),0)
Note: The result of colum (b1) is 2, and when the formula is copied to the right, it can generate 3, 4, 5 ,..
F2 cell formula.
vlookup(b2,$b$14:$f$22,match(f$1,$b$13:$f$13,0))
Formula: Use the match function to find the position of the title of Table 1 in Table 2.
F2 cell formula.
iferror(vlookup($b2,$b$12:$d$16,column(a1),)"")
Formula description: The iferror function is used to mask the wrong value.
vlookup($g96,$a$96:$e$104,column(b1),)
Formula description: Find the corresponding information from the payroll according to the serial number.
g28-3500)*vlookup(g28-3500,c28:e34,2)-vlookup(g28-3500,c28:e34,3)
Formula description: When the last parameter of vlookup is 1 or omitted, the search is an approximate match. That is, to find the value that is smaller and closest than the specified value.
vlookup("*"&f41&"*",$b$41:$d$47,3,0)
Note: * is a wildcard character that represents any number of characters.
vlookup(substitute(f52,"~","~~"),b$52:c$56,2,0)
Note: Since * has a wildcard usage in the formula, when the searched string contains these 3 characters, substitute is replaced with , and ?
vlookup(g61,if(,c61:c69,b61:b69),2,)
Description of the formula: This formula is only for understanding, in case of reverse lookup, it is recommended to use the index+match function.
index(b60:b69,match(g61,c60:c69,0))
text(vlookup(substitute(b73,"*","~*"),$f$73:$g$92,2,)-c73,"0 less00;More than 000;"
Formula description: text can set the format of the number display.
a112 =b112&c112
h112 =vlookup(f112&g112,a112:d120,4,0)
Formula description: vlookup can also use the array form of if( to achieve multi-condition search, but the running efficiency is too slow, so it is recommended to use the auxiliary column join and then find method.
How to set it up: Select Region - Conditional Formatting - New Rule - Determine with formula. Enter the formula in the box below:
h2>vlookup(f2,a:c,3,0)
The last point".Formatbutton to set the fill color to red.
Ten. 2. How to use the OKUP function of VLO February ** dynamic incentive plan to prompt work alarms.
How to set it up
Step 1Add a secondary column to generate an expiration date + sequence based on the expiration date (the purpose is to number the same date ordinally, which is convenient for vlookup one-to-many search).
d2&"-"&countif(d$2:d2,d2)
Step 2Set the expiration date formula, enter =today() h3=h2+1 in h2 cell, and then copy the h3 formula downward.
Step 3Set a summation formula to calculate the total amount of repayment that needs to be repaid at each due date.
sumif(d:d,h2,c:c)
Step 4Use vlookup to search for detailed data in each row.
J2 cell formula.
iferror(vlookup($h2&"-"&j$1,$a:$d,2,0)&":"&vlookup($h2&"-"&j$1,$a:$d,3,0),"")
Description of the formula: Here take the date of column h & the number in row 1 as the search content, search in column a, and then return the data of column b & column c, through the detailed explanation of the vlookup function above, now you know how to use this function in different scenarios?