Excel INDEX和MATCH获取值 [英] Excel INDEX and MATCH Get Value
问题描述
我有一个excel工作簿,我需要一些帮助INDEX和MATCH或任何其他公式,可以让我的最终结果。
I have an excel workbook that I need some help with INDEX and MATCH or any other Formula that can get me my end result.
这里是sheet1: p>
Here is sheet1:
SIT_ID METER SUSE_CD
10834282 DT0061 B
10834282 AW7931 P
21676286 CQ9635 P
21676286 DP4838 B
21726281 AW7880 P
21726281 DT0032 B
这里是Sheet2:
Here is Sheet2:
Site ID B P
10834282
21676286
21726281
最终我想要做的是在Sheet2上,将列表中的SITEID的Meter = B,然后将Meta = P在同一行。
Ultimately what I am trying to do is on Sheet2 is put the Meter that = B for the SITEID in the column and then Put the Meter that = P in the Same row.
我从来没有使用Index或Match,我在网上查找,但我很困惑,希望有人可以帮助我正确的公式或指向正确的方向
I have never used Index or Match and I looked it up online but I am confused and hoping someone can help me with the correct formula or point me in the right direction.
非常感谢!
推荐答案
INDEX
首先需要一个范围,然后是一个行号,一个可选列号(和一个可选的区号)。
INDEX
first takes a range, then a row number, an optional column number (and an optional area number).
MATCH
取值查找,数组和模式。
MATCH
takes a value to lookup, an array and a mode.
在您的问题中,您可以在Sheet2单元格B2中使用以下内容:
In your problem you can use the following in Sheet2 cell B2:
=INDEX(Sheet1!$B$2:$B$7, MATCH($A2, IF(Sheet1!$C$2:$C$7=B$1,Sheet1!$A$2:$A$7), 0))
公式是一个数组公式,它将与 Ctrl + Shift + 输入一起使用,然后可以将其填充到其他单元格。
This formula is an array formula and will work with Ctrl+Shift+Enter and then you can fill it to the other cells.
我必须使用 IF
,因为有两个条件要检查。
I had to use an IF
because there're two conditions to check.
编辑:如果您的单元格格式不同,请使用此选项:
Use this one if your cell formats are different:
=INDEX(Sheet1!$B$2:$B$7,MATCH($A2*1,IF(Sheet1!$C$2:$C$7=B$1,Sheet1!$A$2:$A$7*1),0))
EDIT2:添加修剪:
Adding trimming:
=INDEX(Sheet1!$B$2:$B$7,MATCH($A2*1,IF(TRIM(Sheet1!$C$2:$C$7)=TRIM(B$1),Sheet1!$A$2:$A$7*1),0))
EDIT3:如果您使用完整数据,请更改范围:
If you're using it on your full data, change the range:
=INDEX(Sheet1!$B:$B,MATCH($A2*1,IF(TRIM(Sheet1!$C:$C)=TRIM(B$1),Sheet1!$A:$A*1),0))
这篇关于Excel INDEX和MATCH获取值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!