多列查找 [英] Multicolumn lookup

问题描述

有很多相关的帖子，但我没有找到一个这样的帖子。我目前正在通过excel来帮助加快一个过程。
excel文件有两个电子表格。一个是数据，第二个是摘要。
在数据电子表格中，我将第一列作为名称，接下来的7列包含数据值（并非全部填充）。

There are a lot of posts that relate, but I haven't found one quite like this. I am currently going through excel to help out in speeding up a process a little more. The excel file has two spreadsheets. One is data the second is the summary. On the data spreadsheet, I have the first column as names, and the next 7 columns with data values (Not all filled).

Name     Data1    Data2    Data3   Country    Address
VA       123      456      621     USA        ExampleSt.
MD                123              France     123Street
DC       621                       Korea      999Avenue

UseCol   Value
Data2    456
Data3    621
Data1    000

我的问题是，我的值为621（可以出现在多个列中，但不会出现在） 。我得到了它应该在data1，data2，data3 ......中的哪个集合。如何找到包含该信息的名称？没有VBA，只能通过excel。

My question is, I am given the value "621" (which can appear in multiple columns, but won't appear in ). I am given which set it should be in "data1, data2, data3...". How would I go about finding the name with that information? No VBA, only through excel.

我使用此代码停在这里。

I got stopped here with this code.

=INDEX(A1:D4,MATCH('621',*What do I put here*,0)

For中间部分之所以出现问题是因为我在另一个电子表格中使用了哪个数据列。

For that middle section, the reason why it is a problem is because I'm given which data column to use in another spreadsheet.

EDIT

所以，我已经按照蒂姆·威廉姆斯所说的关于使用偏移量的说法。但是，现在我有第二列我想要去。所以我使用的代码获取名称如下

So, I have followed what Tim Williams has said about using the offset. However, now I have a second column I am trying to get to. So the code that I used to get the Name is as follows

 =IFERROR(INDEX(A2:A4,MATCH(B7,OFFSET(A2:A4,0,MATCH(A7,B1:D1,0)),0,1),"ERROR")

我需要对OFFSET部分进行哪些更改才能查找Country或Address单元？我认为我需要改变的唯一部分是内部MATCH功能。我应该做MATCH（A7，B1） ：D1,0）+3来到Country列？谢谢。

What changes do I have to make to the OFFSET portion to now look for the Country, or the Address cell? I believe the only part I need to change is that inner "MATCH" function. Should I do MATCH(A7,B1:D1,0)+3 to get to Country column? Thank you.

推荐答案

这对我来说很有用g每个数字每列只能出现一次。

This worked for me as long as each number can only appear once per column.

编辑：要获取其他列的匹配值，您只需调整第一列公式的一部分。

EDIT: for getting the matching value from other columns you only need to adjust the first part of the formula.

这篇关于多列查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！