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
vlookup函数的参数及其意义解释vlookup函数的参数及其意义解释Jan 09, 2024 pm 03:18 PM

我们在使用excel的时候一定有用过vlookup函数吧。那么对于这种函总共有几个,每个函数具体是怎么使用的,据小编所知vlookup函数一共有四个,分别是Lookup_value、Table_array、col_index_num、Range_lookup。那么他们的具体用法就让小编来告诉大家吧~vlookup函数有几个参数每个参数的含义vlookup函数的参数有Lookup_value、Table_array、col_index_num、Range_lookup,一共有4个。1、Lookup

vlookup函数的使用方法跨表格匹配vlookup函数的使用方法跨表格匹配Feb 20, 2024 pm 11:30 PM

vlookup函数是Excel中非常常用的函数之一,它可以帮助我们在一个表格中查找指定值,并返回与该值匹配的另一个表格中的对应值。在本文中,我们将探讨vlookup函数的使用方法,特别是在进行跨表格匹配时的应用。首先,让我们来学习一下vlookup函数的基本语法。在Excel中,vlookup函数的语法如下:=vlookup(lookup_value,ta

vlookup函数的使用方法出现错误vlookup函数的使用方法出现错误Feb 21, 2024 pm 12:18 PM

vlookup函数是Excel中非常常用的一种查找函数,用于在指定数据范围中查找某个值,并返回其对应的数据。但是,很多人在使用vlookup函数时可能会遇到一些错误。本文将介绍一些常见的vlookup函数使用错误,并提供解决方法。在使用vlookup函数之前,首先需要了解其语法。vlookup函数的语法如下:=VLOOKUP(lookup_value,ta

公式vlookup怎么用公式vlookup怎么用Feb 19, 2024 pm 10:37 PM

公式VLOOKUP是微软Excel中非常常用的一个函数,它用于在一个表格或数据集中查找特定的值,并返回与之相关联的其他值。在本篇文章中,我们将学习如何正确使用VLOOKUP公式。VLOOKUP函数的基本语法如下:VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])其中:lookup

vlookup函数怎么用-vlookup函数的使用方法vlookup函数怎么用-vlookup函数的使用方法Mar 04, 2024 pm 12:31 PM

很多小伙伴不知道vlookup函数怎么用,所以下面小编就分享了vlookup函数的使用方法,一起去看看吧,相信对大家会有帮助。一、精确查找按照精确信息,在数据表中查询对应数据,图中以姓名查学号,使用的是“=VLOOKUP(F3,A1:D5,4,0)”。二、多条件查找如果表格中存在重复数据时,就要添加上条件来限定查询。图中查找2班李白使用的公式是“=VLOOKUP(F5&G5,IF({1,0},A3:A11&B3:B11,D3:D11),2,FALSE)”。三、反向查找我们想要使用VLOOK

Excel函数公式之VLOOKUP函数的应用Excel函数公式之VLOOKUP函数的应用Mar 20, 2024 am 11:30 AM

函数公式有许多,但是并不是每个都很好用,以前我偏爱用index和match来实现Vlookup能完成的事情,但是当我真正知道了Vlookup这一家人之后,就决定index和match的搭配,转而使用Vlookup函数,现在我就为读者详细介绍一下Vlookup这个函数公式。首先我们要输入公式=Vlookup(H1,条件区域,返回区域列数,精准还是近似),这里的H1就是红玫瑰的位置。  之后我们补充一下公式,这个条件区域要怎么选择呢,这个区域需要条件在第一列,就是我们的红玫瑰这个颜色的一列,必须是这

使用VLOOKUP函数的步骤使用VLOOKUP函数的步骤Feb 19, 2024 pm 03:54 PM

vlookup函数是Excel中非常常用的一种函数,在数据处理和分析中起到了重要的作用。它可以根据指定的关键字在一个数据表中查找匹配的值,并返回相应的结果,为用户提供了快速查找和匹配数据的功能。下面将详细介绍vlookup函数的使用方法。vlookup函数的基本语法是:=VLOOKUP(lookup_value,table_array,col_index

如何使用VLOOKUP函数如何使用VLOOKUP函数Feb 18, 2024 pm 06:41 PM

vlookup是Excel中一种非常有用的函数,它可以根据指定的条件在一列或者一张表中查找相关的数据,并将结果返回。在处理大数据量或者需要进行数据匹配时,vlookup可以大大提高工作效率。要使用vlookup函数,首先需要确定好要查找的数据所在的范围和要匹配的条件。vlookup的语法如下:vlookup(要查找的值,范围,返回列的索引,是否精确匹配

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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!