INDEX/MATCH를 사용한 3차원 룩업

INDEX/MATCH를 사용한 3차원 룩업

이는 이후 삭제된 질문에서 약간 개선되었습니다.

삭제된 투고를 볼 수 있는 분은, https://stackoverflow.com/questions/39793322/three-dimensional-lookup-no-concatenate-or-named-ranges-excel 에서 취득했습니다.

---

이름 있는 범위나 연결 없이 3차원 검색을 하려고 합니다.간단히 말하면, 데이터는 다음과 같은 형식으로 작성됩니다.

    Column1 Column2 Column3
Scott           
P   1       2       3
M   4       5       6
N   7       8       9
George          
P   10      11      12
M   13      14      15
N   16      17      18

이제 특정 이름을 검색한 다음 해당 이름 테이블 내의 특정 문자를 검색한 다음 이 행 번호를 특정 열과 일치시킵니다.

간단한 INDEX/MATCH를 시도했습니다.

=INDEX(A:D,MATCH("M",A:A,0),MATCH("Column1",1:1,0))

은 첫 번째 수 있기 에는 효과가 예로는 효과가 있습니다.M.

다른 이름을 찾으려면 어떻게 수정해야 합니까?

---

아래에 답변했습니다만, 더 좋은 솔루션이 있는지 알고 싶습니다.

사용하였습니다.IF() 「」arrayP행 번호는 다음입니다.George ... row 를 、 '행'MIN() first 첫 function first first function function 、 [ function ]를 취득합니다.P을 사용법

이상으로는 beyond beyond beyond 、 beyond 、 것 、 것 、 것 、 것 beyond beyond beyond 。INDEX()시간 있었어요." " " " " " " " " : )

=INDEX($A$1:$D$9,MIN(IF((ROW(A1:A9)>MATCH($F$4,A1:A9,0))*(A1:A9=$F$5),ROW(A1:A9),"")),MATCH($F$6,$A$1:$D$1,0))

★★★★★★★★★★★★★★★★!
Ctrl+Shift+Enter할 때, 은 "수식 "수식", "으로 됩니다.array★★★★★★ 。

첫 번째 MATCH 내에 있는 다른 두 개의 INDEX/MATCH를 사용하여 조회 범위를 설정할 수 있습니다.그 후 MATCH()를 추가하여 이름의 절대 위치를 구하기만 하면 됩니다.

=INDEX(A:D,MATCH($H$4,INDEX(A:A,MATCH($H$3,A:A,0)):INDEX(A:A,MATCH($H$3,A:A,0)+4),0)+MATCH($H$3,A:A,0)-1,MATCH($H$5,$1:$1,0))

---

이 제품은 더 잘 작동하며 크기 제약이 없습니다.

=INDEX(A:D,MATCH(F4,INDEX(A:A,MATCH(F3,A:A,0)):A1040000,0)+MATCH(F3,A:A,0)-1,MATCH(F5,A1:D1,0))

두 경기 결과를 합산하기만 하면 됩니다.이름에 일치하는 것 하나와 문자에 일치하는 것 하나가 전체 행과 같습니다.

=인덱스(A:D,MATCH(G5,A3:A5,0)+매치(G3,A:A,0),매치(G4,1:1,0)

즉, 다음과 같습니다.색인(모든 데이터, 일치(이름, 이름 열, 정확) + 일치(문자, 문자 열, 정확), 일치(열 이름, 정확)

작업 시트 화면 캡처

제 답변은 일반적인 경우를 시도합니다만, 단 한 가지 주의사항이 있습니다.

한 글자가 단일 문자 텍스트이고 이름이 두 글자 이상일 경우.그렇지 않으면 문자와 이름 사이에 논리적으로 차이가 없다고 느끼고, 그러면 정말로 그렇게 하는 것은 불가능하다.

더 나은 기능 구성을 위한 RE-EDIT:

{=INDEX($A$1:$D$17, MATCH($H$3,$A1:$A17, 0)+MATCH($H$4, INDEX($A1:$A17, MATCH($H$3,$A1:$A17, 0)):INDEX($A:$A, SMALL(IFERROR(MATCH($H$3,$A1:$A17, 0)+POWER(SQRT(IF(LEN($A$1:$A$17)>1, ROW($A$1:$A$17), 0)-MATCH($H$3,$A$1:$A$17, 0)), 2)-1, ROWS($A$1:$A$17)), 2)), 0)-1, MATCH($H$5, $A$1:$D$1, 0))}

A열에 배열 수식을 사용하여 길이가 1보다 큰지 확인하고 행의 num을 배열에 넣습니다.문자에는 0이 지정됩니다.

그런 다음 각각에서 고유한 이름(예: George)의 일치 행을 뺍니다.

그런 다음 min(다른 모든 이름 행 중 마지막 데이터 행이 기본 - 2 매개 변수와 함께 작은 함수)을 사용하여 다음 이름 행(또는 다음 이름이 없는 경우 마지막 데이터 행)을 찾습니다.

나머지는 표준 지수/일치 등

선택한 이름에 해당 문자가 없을 경우 #N/A가 올바르게 반환됩니다.

데이터 세트는 A1:A17, 공식은 매번 A:A를 대신 사용할 수 있지만 IF 내부의 배열 계산에는 A1이 필요합니다.A17 속도.

더 나은 기능 구성을 위해 편집:

데이터 길이가 변경될 때 공식을 편집하지 않으려면 ROWS(A:A)를 통해 계산된 colA의 마지막 데이터 행과 함께 A:A의 전체 열 참조가 전체 구성을 통과하도록 할 수 있습니다.

재편집:

{=INDEX($A:$D, MATCH($H$3,$A:$A, 0)+MATCH($H$4, INDEX($A:$A, MATCH($H$3,$A:$A, 0)):INDEX($A:$A, SMALL(IFERROR(MATCH($H$3,$A:$A, 0)+POWER(SQRT(IF(LEN($A:$A)>1, ROW($A:$A), 0)-MATCH($H$3,$A:$A, 0)), 2)-1, ROWS($A:$A)), 2)), 0)-1, MATCH($H$5,1:1, 0))}

그건 정말 설정에 따라 다르죠

이름에 대한 구분 기호로 공백이 있는 버전에 대해 다시 편집

데이터 결과에는 공백이 없지만 이름이 있는 열 B에서 D에 공백이 표시되는 경우 위의 공식에서 약간만 변경해도 다음과 같이 됩니다.

=INDEX($A$1:$D$17, MATCH($H$3,$A$1:$A$17, 0)+MATCH($H$4, INDEX($A:$A, MATCH($H$3,$A:$A, 0)):INDEX($A:$A, SMALL(IFERROR(MATCH($H$3,$A:$A, 0)+POWER(SQRT(IF($B$1:$B$17="", ROW($A$1:$A$17), 0)-MATCH($H$3,$A$1:$A$17, 0)), 2)-1, ROWS($A$1:$A$17)), 2)), 0)-1, MATCH($H$5, $A$1:$D$1, 0))

즉, 이름 및 문자는 지정된 길이일 필요는 없지만 이름과 함께 행에 공백이 표시된다는 한 가지 조건이 있습니다.

과 같이할 끝 :SQRT(IF(LEN($A$1:$A$17)>1,뭇매를 맞다

SQRT(IF($B$1:$B$17="",

Index()의 영역(4번째 파라미터)을 사용합니다.아래는 테스트 데이터의 스크린샷입니다.이 예에서는 동일한 열과 키가 정렬되고 일관된다고 가정합니다.

이것은 인덱스의 첫 번째 파라미터로 (Range1,Range2)를 사용하여 동작합니다.인덱스의 네 번째 파라미터로 인덱스를 반환할 () 영역에 N을 사용합니다.

이게 좀 더 깔끔하고 수정하기 쉬울 것 같아요.

=INDEX(OFFSET(INDIRECT("A"&MATCH($H$3,$A:$A,0),TRUE),0,0,4,4),MATCH($H$4,$A:$A,0),MATCH(H5,$1:$1,0))

오프셋을 사용하여 먼저 범위를 생성하면 H3의 이름을 사용하여 범위를 설정하고 그 이후로는 새로운 범위 내에서 인덱싱할 수 있습니다.

이것은 여전히 이름 A열에 남아 있는 것에 의존합니다.

데이터 형식이 항상Name그리고나서P,M그리고.N이 공식은 다음과 같이 작용합니다.

=INDEX($A:$D,
MATCH($H$3,$A:$A,0)
+LOOKUP($H$4,{"P",1;"M",2;"N",3}),
MATCH($H$5,$1:$1,0))

이 솔루션은 거의 모든 조건에서 작동합니다.제가 발견한 한 가지 제약사항은 제목(이름) 중 하나에 세부사항(문자)에 대한 데이터가 없는 경우입니다만, 현재로선 다른 모든 답변에 대해서도 마찬가지입니다.

공식은 데이터가 다음 위치에 있다고 가정합니다.B6:F30(소스 범위 위치에 관계없이 적용할 수 있도록 하기 위해).

공식은 Index\Match 함수를 사용합니다.

첫 번째로, MATCH를 사용하여 의 위치를 취득합니다.Name:

MATCH($H8,$B$6:$B$30,0)

이 정보를 사용하여 INDEX를 사용하여 위치를 얻기 위해 사용되는 범위를 구축합니다.Detail(문자) 두 번째 MATCH 함수를 사용합니다.

+ MATCH($I8,INDEX($B$6:$B$30, 1 + MATCH($H8,$B$6:$B$30,0))
:INDEX($B$6:$B$30,ROWS($B$6:$B$30)),0),

첫 번째와 두 번째 MATCH 함수의 결과를 더하면 이 함수의 위치를 얻을 수 있습니다.Name"Detail" 조합은 전체 데이터에 대한 인덱스에서 사용됩니다.필요한 데이터 열의 위치는 일치 항목을 사용하여 얻을 수 있습니다.

INDEX($B$6:$F$30, 1st.MATCH + 2nd.MATCH,
MATCH(J$6,$B$6:$F$6,0))

결과는 다음 위치에 있습니다.G6:L30이 식을 에 입력하다J8그 후 복사하다J8:L30:

= INDEX( $B$6:$F$30,
MATCH( $H8, $B$6:$B$30, 0)
+MATCH( $I8, INDEX( $B$6:$B$30 , 1 + MATCH( $H8, $B$6:$B$30 ,0))
: INDEX( $B$6:$B$30, ROWS($B$6:$B$30) ),0),
MATCH( J$6, $B$6:$F$6, 0)),"")

이 솔루션은 지금까지 설명한 모든 조건에서 동작합니다(동작하지 않는 조건이 있으면 알려주시면 대응하겠습니다).이러한 특수한 시나리오에서는 사용자에게 유용하기 때문에, 이러한 긴 수식을 적용할 필요가 없기 때문에, 선행 회답에 적용된 수식이, 이러한 수식을 적용할 필요가 없기 때문에, 별도 회답으로서 투고합니다.

이 공식은 데이터가 다음 위치에 있다고 가정합니다.B6:E30 (소스 범위 위치에 관계없이 적용할 수 있도록 하기 위해).

이 수식은 Index\Match 함수를 사용하며 수식 배열입니다.

Formula Array를 입력했습니다. [Ctrl] + [Shift] + [Enter] 동시에, 당신은 알게 될 것이다. { 그리고. } 올바르게 입력된 경우 공식 주위에

구문:

=IFERROR(INDEX(DataRng,
MATCH(Value1,NamesRng,0)
+IFERROR(MATCH(Value2,INDEX(NamesRng,
1+MATCH(Value1,NamesRng,0))
:INDEX(NamesRng, IFERROR(MATCH(Value1,NamesRng,0)
+MATCH("#",IF((INDEX(Col1Rng,1+MATCH(Value1,NamesRng,0))
:INDEX(Col1Rng,ROWS(NamesRng)))="","#","!"),0),
ROWS(NamesRng))),0),NA()),MATCH(ValCol,DataHdr,0)),"")

인수: 데이터가 B6에 있다고 가정하면:E30.

Value1=Name데이터(예: George, Scott 등)에서 찾을 수 있습니다.

Value2=Detail참조할 수 있습니다.상세1, 디탈레2 등

ValCol=Column참조할 수 있습니다.열 1, 열 2 등

DataRng=$B$6:$E$30

DataHdr=$B$6:$E$6

NamesRng=$B$6:$B$30

Col1Rng=$C$6:$C$30

첫 번째 MATCH: 이름 위치를 검색합니다.

MATCH(Value1,NamesRng,0)

두 번째 MATCH: 열의 빈 값으로 결정되는 이름에 해당하는 세부 정보의 끝 위치를 검색합니다.C「 」 「 」 、 「 」

MATCH("#",IF((INDEX(Col1Rng, 1 + 1stMATCH)
:INDEX(Col1Rng,ROWS(NamesRng)))="","#","!"),0),

범위(vRange):첫 번째와 두 번째 일치 기능을 사용한 이름 세부 정보.두 번째 일치에서 오류가 반환되면 데이터 범위의 마지막 행을 사용합니다.

INDEX(NamesRng, 1 + 1stMATCH )
:INDEX(NamesRng, IFERROR( 1stMATCH + 2ndMATCH, ROWS(NamesRng)))

3rd MATCH: 의 위치를 검색합니다.DetailvRange 내 v v v v v 。#NA조합이 존재하지 않는 경우.

IFERROR(MATCH(Value2, vRange,0), NA())

및 세 하면 행 됩니다.Namecombination or#NAif no found. The Column index is obtained with a Match from the Header of the Data. It then applying the INDEX function to the Data Range returns the value of the[(Column.combination. If theName\Detail(이름)\Detail(상세)을 클릭합니다.빈칸이 반환됩니다.

=IFERROR( INDEX( DataRng, 1stMATCH + 3rdMATCH, MATCH(Column,DataHdr,0)),"")

결과는 H6에 있습니다.L37은 J8에 다음 Formula Array를 입력하고 K8에 복사합니다.L37 및 J9:L37:

=IFERROR( INDEX($B$6:$E$30,
MATCH($H8,$B$6:$B$30,0)
+IFERROR( MATCH($I8, INDEX($B$6:$B$30,
1+MATCH($H8,$B$6:$B$30,0))
:INDEX($B$6:$B$30, IFERROR(MATCH($H8,$B$6:$B$30,0)
+MATCH("#", IF((INDEX($C$6:$C$30,1+MATCH($H8,$B$6:$B$30,0))
:INDEX($C$6:$C$30,ROWS($B$6:$B$30)))="","#","!"),0),
ROWS($B$6:$B$30))),0),NA()),
MATCH(J$6,$B$6:$E$6,0)), "")

와... 벌써 해결 방법이 많네요

더 간단한 해결책은 더 일반적인 답을 얻기 위해 오프셋을 사용하는 것이라고 생각합니다.

=INDEX($A$1:$D$9, MATCH($G$3,OFFSET($A$1,MATCH($G$2,$A$1:$A$9,0),0,3,1),0)+MATCH($G$2,$A$1:$A$9,0), MATCH($G$4,$B$1:$D$1,0)+1)

검색해야 할 변수는 행 수에 영향을 미치기 때문에 존재하는 M/N/P 옵션의 수인 3뿐입니다.그렇지 않으면 가능한 모든 시나리오와 다양한 순서로 올바르게 동작합니다.

데이터 검색용 입력구가 3개 이상 있는 경우 그림과 같이 데이터를 정리하여 원하는 대로 피벗 테이블을 사용하여 데이터를 행과 열로 정리할 수 있도록 하는 것이 좋습니다.

저는 ★★★★★★★★★★★★★★★★★★★.GETPIVOTDATA값을 검색합니다.

방G9는음

=GETPIVOTDATA("Value";$F$3;"Name";G15;"Letter";G16;"Column";G17)

언급URL : https://stackoverflow.com/questions/39794205/three-dimensional-lookup-using-index-match