여러 열에 걸친 INDEX 및 MATCH

여러 열에서 값을 일치시켜 검색하려면, MMULT, TRANSPOSE, 그리고 COLUMN을 활용한 INDEX 및 MATCH 함수 기반의 배열 수식이 도움이 될 것입니다.

여러 열에 걸쳐 값을 일치시켜 검색하는 방법은 무엇입니까?

위 표와 같이 각 학생의 해당 클래스를 채우기 위해, 정보가 여러 열에 나열된 경우, 먼저 MMULT, TRANSPOSE 및 COLUMN 함수의 트릭을 사용하여 행렬 배열을 생성할 수 있습니다. 그런 다음 MATCH 함수는 조회 값의 위치를 제공하며, 이를 INDEX에 전달하여 배열에서 원하는 값을 검색합니다.

일반적인 구문

=INDEX(결과_범위,(MATCH(1,MMULT(--(조회_배열=조회_값),TRANSPOSE(COLUMN(조회_배열)^0)),0)))

√ 참고: 이는 배열 수식으로 Ctrl + Shift + Enter로 입력해야 합니다.

지미의 클래스를 찾으려면, 아래 수식을 H5 셀에 복사하거나 입력하고, 결과를 얻기 위해 Ctrl + Shift + Enter를 누르세요:

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

√ 참고: 위의 달러 기호($)는 절대 참조를 나타내며, 이는 수식을 다른 셀로 이동하거나 복사해도 수식의 이름 및 클래스 범위가 변경되지 않음을 의미합니다. 조회 값에 대한 셀 참조에는 달러 기호를 추가하지 않아야 합니다. 이는 수식을 다른 셀로 복사할 때 상대적으로 유지하기 위해서입니다. 수식을 입력한 후, 채우기 핸들을 아래로 드래그하여 수식을 아래 셀에 적용하세요.

수식 설명

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5): 이 세그먼트는 범위 내 각 값을 확인합니다. $C$5:$E$7 G5 셀의 값과 동일한지 확인하고 다음과 같은 TRUE 및 FALSE 배열을 생성합니다:
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  이중 음수는 TRUE 및 FALSE를 1과 0으로 변환하여 다음과 같은 배열을 생성합니다:
  {1,0,0;0,0,0;0,0,0}.
• COLUMN($C$5:$E$7): COLUMN 함수는 $C$5:$E$7 범위의 열 번호를 배열로 반환합니다: {3,4,5}.
• TRANSPOSE(TRANSPOSE(COLUMN($C$5:$E$7)^0)^0) = TRANSPOSE(TRANSPOSE({3,4,5}^0)^0): 0 제곱 후, 배열 {3,4,5}의 모든 숫자는 1로 변환됩니다: {1,1,1}. TRANSPOSE 함수는 열 배열을 다음과 같은 행 배열로 변환합니다: {1;1;1}.
• MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPOSE(COLUMN($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): MMULT 함수는 두 배열의 행렬 곱을 반환합니다: {1;0;0}.
• MATCH(1,MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0),0) = MATCH(1,MATCH(1,{1;0;0},0),0): 매치_타입 0은 MATCH 함수가 배열 {1;0;0}에서 1의 첫 번째 일치 위치를 반환하도록 강제하며, 그 값은 1입니다.
• INDEX($B$5:$B$7$B$5:$B$7,,,(MATCH(1,MMULT(--($C$5:$E$7=G5),,,TRANSPOSE(COLUMN($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7$B$5:$B$7,,,1): INDEX 함수는 클래스 범위 $B$5:$B$7의 첫 번째 값을 반환하며, 그 값은 A입니다.

여러 열에 걸쳐 값을 쉽게 검색하려면, 당사의 전문 Excel 추가 기능인 Kutools For Excel을 사용할 수도 있습니다. 임무를 완수하기 위한 지침은 여기를 참조하세요.

관련 함수

관련 수식