How vlookup matches multiple columns of data

If we are given one column of data now, but we are asked to match multiple columns of data, how should we solve this problem?

The above questions are the examples we are going to explain today, so next we will directly enter the example explanation stage.

Example: We now have such an excel worksheet, which contains two tables. The first table is a data source table, which includes five items: customer ID, company name, contact name, address and contact title, along with related data. The second table contains four items, respectively. It is the customer ID, company name, contact name and address, where the customer ID is known content, while the company name, contact name and address are unknown content. Now our task is to based on the data source in the first table and the second table For the customer ID in the table, use the function vlookup to match the company name, contact name and address. The excel worksheet is as follows:

Example picture

Here, I recommend two methods to solve this problem.

Method 1: In H2 cell, I2 cell and J2 cell respectively, you can also use the function vlookup to get the corresponding results, and then use the drag function of the fill handle to get All cells to be matched.

The specific operation method is as follows: First, we enter "=VLOOKUP(G2,$A$1:$E$16,2,0)", "= in cell H2, cell I2 and cell J2 in sequence VLOOKUP(G2,$A$1:$E$16,3,0)", "=VLOOKUP(G2,$A$1:$E$16,4,0)", then we press the Enter key to get the customers respectively The company name, contact name and address corresponding to the ID "BERGS", then we select the H2 cell, I2 cell and J2 cell, and then drag down by dragging the fill handle, we can go to other The company name, contact name and address corresponding to the customer ID. For specific operations, please refer to the picture below:

Example picture

Method evaluation: The above method combines the basic usage of the function vlookup with the dragging function of the fill handle. It solves the existing problems, but it still has great limitations. Just imagine, here we have to match three items of data, and we end up writing three formulas. If we match 100 items of data, I'm afraid we don't have the patience to write another 100 formulas. So let’s look at the more convenient method two next!

Method 2: Here we only need to fill in the appropriate function formula in the H2 cell, and then use the fill handle to drag left and down, so that all results can be obtained . But in this process we will encounter two major problems: How to make mixed references to the first parameter? How to determine the third parameter? Next, we will solve the problem while making a table.

First, we make the answer to cell H2. Enter "=VLOOKUP(G2,$A$1:$E$16,2,0)" in cell H2 and press Enter. At this time, based on past experience, we know that if we drag down next, the result will still not be wrong, so the key question is how to ensure that dragging to the left will not go wrong.

We select cell H2 and drag one cell to the left to see what the result is?

Example picture

The result is #NA, the specific function formula is "=VLOOKUP(H2,$A$1:$E$16,2,0) ", from this functional formula, we can see two errors. First, the first parameter should be "G2", not "H2", and secondly, the third parameter should be "3", not "2".

First of all, let’s solve the problem caused by the first parameter. Some people may say that it can be changed to $G$2 (absolute reference). This does solve the problem caused by dragging to the left, but it also It will cause an error when dragging down, so we need to use mixed references to solve the problem. Rewrite "G2" to "$G2" and lock the column.

Now we come to the second question, how to make the third parameter change continuously as the fill handle is dragged? We can see from the functional formula "=VLOOKUP(H2,$A$1:$E$16,2,0)" that if you just fill in the numbers in the function vlookup, it will not continue to change as the fill handle is dragged. , so we still need to use the functions of functions.

excel

Here I recommend using the function column. Its basic grammatical form is COLUMN(reference). Specifically, we can look at the following three examples: "=COLUMN()" will get the formula. column; "=COLUMN(A10)" will get the result "1" because column A is the first column; "=COLUMN(C3:D10)" will get the column number of the first column in the reference, which is "3". What we want to use here is "=COLUMN()".

Here when we enter "=COLUMN()" in cell H2, we will get "8", because column H is the eighth column, but the third parameter here should be "2", so the specific value of the third parameter The format should be "=COLUMN()-6". At this time, the functional formula to be filled in cell H2 will become "=VLOOKUP($G2,$A$1:$E$16,COLUMN()-6,0)" ". When dragging to the left, the first parameter G2 remains unchanged, and the third parameter "COLUMN()-6" increases accordingly; when dragging downward, the first parameter changes accordingly, and the third parameter "COLUMN() )-6" remains unchanged, such a functional expression meets all requirements.

Specific method arrangement: First, we enter "=VLOOKUP($G2,$A$1:$E$16,COLUMN()-6,0)" in cell H2, and then we press the Enter key. We can get the company name corresponding to the customer ID "BERGS" respectively. Then we select the H2 cell and drag it to the left to get the contact name and address corresponding to the customer ID "BERGS". Finally, we select cell H2, cell I2 and cell J2, and then drag down by dragging the fill handle. We can get the company name, contact name and address corresponding to other customer IDs. For specific operations, please refer to the picture below:

Example picture

Summary:

1. First of all, we must be very proficient in using the function vlookup For basic operation methods, if you are interested, you can refer to the article Thousands of data are fascinating, and the function vlookup will help you choose!

2. The issues of relative reference, absolute reference and mixed reference of cell content in excel must be clearly distinguished. You can refer to the article Excel about the clever use of absolute reference and mixed reference.

3. To understand Understand the basic usage of function column.

Today’s sharing ends here, interested friends can like and follow!

For more technical articles related to frequently asked questions, please visit the FAQ column to learn more!

The above is the detailed content of How vlookup matches multiple columns of data. For more information, please follow other related articles on the PHP Chinese website!