사람들이 잘 모르는 엑셀 팁(Tip) 알아보기 : 조건에 맞는 모든 값 참조해오기

 

 

 

 

 

VLOOKUP이 INDEX 함수는 조건에 맞는 첫 번째 값을 참조한다. 하지만 필요에 따라 조건을 만족하는 값을 순서로 모두 참조해야 할 때가 있다. 이런 경우에는 필터로 원하는 데이터를 축출하는 것이 편리하지만 배열 수식을 이용할 수도 있다.

 

모든 값 참조.xlsx

0.01MB

 

위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다.

 

 

[I1] 셀의 [서울 구이 김]을 구입한 고객 목록을 왼쪽 표를 참조하여 [I4:I6] 범위에 수식을 참조해 보자.

 

 

첫 번째 고객명은 INDEX, MATCH 함수 조합을 이용하여 수식을 작성한다. [I4] 셀을 선택하고 다음 수식을 입력한다.

 

=INDEX($B$2:$B$15,MATCH($I$1,C2:C15,0))

 

이 수식에서 MATCH 함수는 [C2:C18] 범위에서 제품명[I1]과 정확하게 일치하는 값이 몇 번째에 있는지 확인한다. INDEX 함수 [B2:B18] 범위의 해당 위치 값을 반환하도록 한다. 조건과 일치하는 첫 번째 값을 참조해오는 것은 이와 같이 기존에 배웠던 수식을 이용하면 된다. 하지만 두 번째, 세 번째는 일반 수식을 사용할 수 없으므로 배열수식을 사용해야 한다.

 

 

두 번째, 세 번째 고객명을 배열수식을 이용하여 구할 수 있다. [I5] 셀을 선택하고 다음 수식을 작성한후 Ctrl + Shift + Enter를 눌러서 배열수식을 입력한다. [I5]셀의 채우기 핸들을 [I6]셀까지 드래그하여 수식을 복사한다.

 

=INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=$I$1,ROW($C$2:$C$15)-1),H5))

 

[B2:B15] 범위에서 값을 참조할 때, 여러 개의 값이 있으므로 SMALL 함수를 사용해 조건과 일치하는 행 번호 중에서 n번째 행 위치를 확인하는 수식이다. 이수식의 계산과정은 다음과 같다.

 

① (IF($C$2:$C$15=$I$1, : [C2, C5] 범위에서 [I1] 셀과 같은 제품명을 갖고 있는 셀인지를 확인한다.

 

② ROW($C$2:$C$15)-1) : ①의 결과가 참(TRUE)인 경우에 [C2:C15] 범위의 행 번호를 확인한다. [2]행부터 시작하므로 MATCH함수와 값이 몇 번째에 있는지를 가리키려면 행 번호에서 1을 빼준다. 그러면 [4, 8, 15] 배열에 1을 빼주는 효과가 있으므로 [3, 7, 17]에서 두 번째로 작은 값은 7이다.

 

③ SMALL(②,H5) : SMALL 함수를 사용해 ②에서 반환된 배열 중에서 [H5] 셀의 값과 같이 두 번째로 작은 값을 반환하도록 한다. [3, 7, 17]에서 두 번째로 작은 값은 7이다.

 

④ INDEX(B2:B15,③) : INDEX 함수로 [B2:B15] 범위에서 7번째 값을 반환하도록 한다.

 

이렇게 하면 몇 번째 위치에 있는 값이라도 모두 참조할 수 있다. 수식이 제대로 동작하려면 배열수식 Ctrl + Shift + Enter 으로 작성해야 한다.

 

Tip : 배열 수식이 제대로 작동하지 않을 경우가 발생하는데 그 이유는 아래의 사진과 같이 '한국어 Microsoft 입력기'로 설정되어 있어야만 제대로 작동을 하는데 '한국어 한컴 입력기'로 설정된 경우 작동하지 않는다.