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!

This tutorial explains how to calculate the median of numerical data in Excel using the MEDIAN function. The median, a key measure of central tendency, identifies the middle value in a dataset, offering a more robust representation of central tenden

Master Google Sheets COUNTIF: A Comprehensive Guide This guide explores the versatile COUNTIF function in Google Sheets, demonstrating its applications beyond simple cell counting. We'll cover various scenarios, from exact and partial matches to han

This tutorial provides a comprehensive guide to sharing Excel workbooks, covering various methods, access control, and conflict resolution. Modern Excel versions (2010, 2013, 2016, and later) simplify collaborative editing, eliminating the need to m

This tutorial explores various methods for converting .xls files to .jpg images, encompassing both built-in Windows tools and free online converters. Need to create a presentation, share spreadsheet data securely, or design a document? Converting yo

This tutorial clarifies the function of Excel names and demonstrates how to define names for cells, ranges, constants, or formulas. It also covers editing, filtering, and deleting defined names. Excel names, while incredibly useful, are often overlo

This tutorial clarifies the distinction between standard deviation and standard error of the mean, guiding you on the optimal Excel functions for standard deviation calculations. In descriptive statistics, the mean and standard deviation are intrinsi

This Excel tutorial demonstrates how to calculate square roots and nth roots. Finding the square root is a common mathematical operation, and Excel offers several methods. Methods for Calculating Square Roots in Excel: Using the SQRT Function: The

Unlock the Power of Google Sheets: A Beginner's Guide This tutorial introduces the fundamentals of Google Sheets, a powerful and versatile alternative to MS Excel. Learn how to effortlessly manage spreadsheets, leverage key features, and collaborate


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
