How to use the vlookup function How to use the vlookup function

In Excel table processing, the VLOOKUP function is a powerful tool for finding and extracting data in tables. PHP editor Xiaoxin will introduce you in detail how to use the VLOOKUP function to help you easily master this practical skill. From basic syntax to usage tips, we break it down one by one to give you an in-depth understanding of the powerful functions of the VLOOKUP function. Continue reading below to master the essence of using the VLOOKUP function and improve your data processing efficiency!

If there is duplicate data in the table, conditions must be added to limit the query. The formula used to find Class 2 Li Bai in the picture is [=VLOOKUP(F5&G5,IF({1,0},A3:A11&B3:B11,D3:D11),2,FALSE)].

3. Reverse search

We want to use VLOOKUP to query Li Bai’s job number, which is a reverse search, using the known name to find the job number on the left , the formula is [=VLOOKUP(F5,IF({1,0},B3:B11,A3:A11),2,FALSE)].

4. One-to-many query

One piece of information matches multiple employees, and you can view employee names by section. The picture below takes the marketing department as an example to query all employees in the marketing department. Name, the formula is [=IFERROR(VLOOKUP(ROW(A1),$A$2:$E$11,4,0),"")].

5. Automatically match the third parameter

Use the function once to get multiple rows and columns of data. You can query the department and name through the job number. The formula is [=VLOOKUP($F3,$A$2:$D$13,MATCH(H$2,$A$2:$D$2,0),FALSE)]

6. Wildcard search

Wildcards are commonly used symbols

: represents any 1 character

*: represents any multiple characters

We can use wildcards to perform fuzzy searches when you are in information memory Used when fuzzy. If you don’t know the name, you can use this method to query. The formula in the picture is [=VLOOKUP(F4,B3:C11,2,0)].

7. Interval query

Interval query needs to build the interval first. The green marked data table in the figure is the interval. At this time, you can query the corresponding commission, but you need to Note that the fourth parameter is 1, and the formula is [=VLOOKUP(B4,$E$11:$F$16,2,TRUE)].

8. Data Extraction

Obtain data from a bunch of characters, exclude non-numeric content, and identify valid information. The formula is [=VLOOKUP(0,MID( A3,ROW($1:$102),11)*{0,1},2,FALSE)].

9. Find the maximum/nearest value

When looking for the maximum and minimum sales on a certain day, first use the [Sort] tool to [sort in descending order], and then use the formula [ =VLOOKUP(F3,A2:C14,3,0)].

10. Skip empty characters and search

If there are spaces in the string, the result #N/A will be returned. In this case, you need to combine the TRIM function to clear the spaces in the text. Then query the data and query Zhang 3’s basic salary in the data table with spaces. The formula is [=VLOOKUP(TRIM(G2),TRIM(B1:E6),4,FALSE)].

11. Merge cell query

To merge different cell data for query, you need to use the [INDIRECT] function to jump. The picture shows the situation where the name is known. Next, unify the data and query the results of Li Bai in Class 2. The formula is [=VLOOKUP(G5,INDIRECT("b"&MATCH(F5,A:A,0)&":D11"),3,0)] .

The above is the detailed content of How to use the vlookup function How to use the vlookup function. For more information, please follow other related articles on the PHP Chinese website!