search
HomeTopicsexcelHow vlookup matches multiple columns of data

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:

How vlookup matches multiple columns of data

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:

How vlookup matches multiple columns of data

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?

How vlookup matches multiple columns of data

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:

How vlookup matches multiple columns of data

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!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MEDIAN formula in Excel - practical examplesMEDIAN formula in Excel - practical examplesApr 11, 2025 pm 12:08 PM

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

Google Spreadsheet COUNTIF function with formula examplesGoogle Spreadsheet COUNTIF function with formula examplesApr 11, 2025 pm 12:03 PM

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

Excel shared workbook: How to share Excel file for multiple usersExcel shared workbook: How to share Excel file for multiple usersApr 11, 2025 am 11:58 AM

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

How to convert Excel to JPG - save .xls or .xlsx as image fileHow to convert Excel to JPG - save .xls or .xlsx as image fileApr 11, 2025 am 11:31 AM

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

Excel names and named ranges: how to define and use in formulasExcel names and named ranges: how to define and use in formulasApr 11, 2025 am 11:13 AM

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

Standard deviation Excel: functions and formula examplesStandard deviation Excel: functions and formula examplesApr 11, 2025 am 11:01 AM

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

Square root in Excel: SQRT function and other waysSquare root in Excel: SQRT function and other waysApr 11, 2025 am 10:34 AM

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

Google Sheets basics: Learn how to work with Google SpreadsheetsGoogle Sheets basics: Learn how to work with Google SpreadsheetsApr 11, 2025 am 10:23 AM

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

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

MantisBT

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

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

mPDF

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

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.