VLOOKUP 여러 조건을 만족하는 여러개 값 반환하는 공식 - VLOOKUP yeoleo jogeon-eul manjoghaneun yeoleogae gabs banhwanhaneun gongsig

이 경우에 VLOOKUP 함수나 INDEX, MATCH 함수를 이용한 단일 조건으로는 값을 찾을 수 없고 INDEX, MATCH 함수와 배열 수식을 조합하면 값을 찾을 수 있습니다.

실습용 엑셀파일 다운로드 : 엑셀-INDEX-MATCH-함수를-이용한-다중조건으로-값찾기.xlsx

※ 예제 이해에 필요한 내용은 다음 링크를 참고하세요.
[ VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기 ] - INDEX, MATCH를 이용한 단일 조건으로 값찾기
[ 배열수식 제대로 이해하기 ] - 배열 수식 기초
[ INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 ]
[ MATCH 함수 - 범위에서 값의 위치 찾기 ]

1. 찾는 조건을 TRUE, FALSE 바꾸어서 찾기

찾고자 하는 값을 배열과 비교하여 맞으면 TRUE, 아니면 FALSE로 바꾸어서 찾는 방법입니다.

이 방법은 배열수식, INDEX, MATCH 함수 등 사전에 알아야 할 것이 많지만 알아 두면 복잡한 조건으로 값을 찾을 때 많은 도움이 됩니다.

판매 연도, 상품코드에 해당하는 단가를 찾기 위해 [E19] 셀에 다음 수식을 입력합니다.

=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))

배열 수식이므로 수식 입력 후 [Enter] 대신에 [Ctrl+Shift+Enter]를 눌러야 합니다.
* Microsoft 365, 엑셀 2021 이상 버전에선 [Enter]만 눌러도 됩니다.
수식이 제대로 입력되면 배열수식을 의미하는 중괄호가 수식 양쪽에 붙어 있습니다.
{=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))}

수식이 제대로 입력되었다면 다음과 같이 상품코드가 'A002'이고 적용연도가 2020년인 단가 35,000원을 가져옵니다.

배열수식을 이용해서 값을 가져왔는데 이 복잡한 수식이 어떻게 작동되는지 확인해보겠습니다.

INDEX 함수안에 MATCH 함수가 포함되어 있으므로 MATCH 함수가 먼저 실행됩니다.
=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))

위의 수식에서 찾는 조건을 값으로 바꾸면 다음과 같이 됩니다.
=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14="A002")*($D$6:$D$14="2020"),0))

아래와 같이 수식의 파란색 부분은 서로의 결과를 곱하는 것입니다.
($B$6:$B$14="A002")*($D$6:$D$14="2020")

곱하기 전에 앞 부분과 뒷 부분을 떼어서 그림으로 표현하면 다음과 같습니다.
($B$6:$B$14="A002") => 상품코드가 'A002'이면 TRUE로 바뀌고 아니면 FALSE로 바뀝니다.
($D$6:$D$14="2020") => 적용연도가 2020년이면 TRUE로 바뀌고 아니면 FALSE로 바뀝니다.

상품코드와 적용연도 조건을 비교해서 TRUE, FALSE로 바꾸고 서로를 곱하면 결과는 다음과 같습니다.
엑셀에서 TRUE는 1, FALSE는 0으로 인식합니다.

($B$6:$B$14="A002")*($D$6:$D$14="2020")

결과적으로 상품코드가 'A002'이고 적용연도가 2020년인 것을 만족하는 것은 4번째 행이 됩니다.

위의 결과를 배열로 표현하면 {0;0;0;1;0;0;0;0;0} 와 동일합니다.

따라서 이 수식은
=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14="A002")*($D$6:$D$14="2020"),0))

다음과 같이 표현할 수 있고,
=INDEX($E$6:$E$14,MATCH(1,{0;0;0;1;0;0;0;0;0},0))

INDEX 함수 안의 MATCH 함수를 해석해 보면 1이라는 값을 {0;0;0;1;0;0;0;0;0} 범위에서 몇 번째 있는지 찾는데 4번째 행에 있으므로 4를 반환합니다.
MATCH(1,{0;0;0;1;0;0;0;0;0},0) => 4

MATCH 함수의 결과 4를 받아서 수식은 다음과 같이 바뀝니다.
=INDEX($E$6:$E$14, 4)

최종적으로 INDEX 함수는 단가 범위 [$E$6:$E$14]의 4번째 행의 35,000원을 구해줍니다.

2. & 기호(결합연산자)로 묶어서 찾기

앞에서 살펴본 TRUE, FALSE로 바꾸는 방식보다는 간단하고 이해하기 쉬운 방식입니다.

판매 연도, 상품코드에 해당하는 단가를 찾기 위해 [E28] 셀에 다음 수식을 입력합니다.
=INDEX($E$6:$E$14,MATCH(C28&B28,$B$6:$B$14&$D$6:$D$14,0))

배열 수식이므로 수식 입력 후 [Enter] 대신에 [Ctrl+Shift+Enter]를 눌러야 합니다.
* Microsoft 365, 엑셀 2021 이상 버전에선 [Enter]만 눌러도 됩니다.
수식이 제대로 입력되면 배열수식을 의미하는 중괄호가 수식 양쪽에 붙어 있습니다.
{=INDEX($E$6:$E$14,MATCH(C28&B28,$B$6:$B$14&$D$6:$D$14,0))}

수식이 제대로 입력되었다면 다음과 같이 상품코드가 'A002'이고 적용연도가 2020년인 단가 35,000원을 가져옵니다.

배열수식을 이용해서 값을 가져왔는데 수식이 어떻게 작동되는지 확인해보겠습니다.

이 방식은 '단일 조건으로 찾기'와 같은 방식으로 이해할 수 있습니다.

차이점은 다음과 같이 상품코드만 가지고 비교하던 것을
"A002"

상품코드에 '&' 기호로 연도를  묶어서 비교하는 것만 차이가 있을 뿐 기본 개념이 동일합니다.
"A002"&2020 => 'A0022020'

따라서 최초 입력된 다음 수식은
=INDEX($E$6:$E$14,MATCH(C28&B28,$B$6:$B$14&$D$6:$D$14,0))

다음과 같이 표현할 수 있습니다.
=INDEX($E$6:$E$14,MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0))

INDEX 함수안에 MATCH 함수가 포함되어 있으므로 MATCH 함수가 먼저 실행됩니다.
MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0)

위 수식의 $B$6:$B$14&$D$6:$D$14부분을 그림으로 표현하면 다음과 같이 내부적으로 2개의 범위가 합쳐져서

아래와 같이 배열이 만들어 집니다.

이제 MATCH 함수에서 'A0022020' 라는 값을 아래 그림의 배열에서 찾으면
4번째 행에 있으므로 4를 반환합니다.
MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0) => 4

결과적으로 상품코드가 'A002'이고 적용연도가 2020년인 것을 만족하는 것은 4번째 행이 됩니다.

따라서 다음 수식에서
=INDEX($E$6:$E$14,MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0))

MATCH 함수의 결과 4를 받으면 수식은 다음과 같이 바뀝니다.
=INDEX($E$6:$E$14, 4)

최종적으로 INDEX 함수는 단가 범위 [$E$6:$E$14]의 4번째 행의 35,000원을 구해줍니다.

[ 찾기 및 참조영역 함수 목록 ]

• 찾기 및 참조영역 함수 알아보기
• 행과 열 조건을 만족하는 값 찾기
• 엑셀에서 대소문자 구분해서 찾기
• VLOOKUP 함수 사용법
• OFFSET 함수로 월별 누계 구하기
• UNIQUE 함수 - 중복 제거하기
• FORMULATEXT 함수 - 수식을 텍스트로 표시
• XMATCH 함수 사용법
• FILTER 함수 - 원하는 조건으로 필터링하기
• SORTBY 함수 - 범위의 값을 기준으로 데이터 정렬하기
• SORT 함수 - 데이터 정렬하기
• XLOOKUP 함수 - 표에서 값찾기(VLOOKUP 단점 해결)
• HLOOKUP 함수 - 표를 수평으로 따라가면서 값찾기
• HYPERLINK 함수 - 하이퍼링크 만들기
• ROWS 함수 - 행의 개수 구하기
• COLUMNS 함수 - 열의 개수 구하기
• AREAS 함수 - 참조영역내의 영역의 개수 구하기
• ADDRESS 함수 - 행,열 번호로 셀주소 표시하기
• TRANSPOSE 함수 - 행과 열을 바꾸기
• INDIRECT 함수 – 문자열을 참조로 바꾸기
• LOOKUP 함수 - 범위에서 값 찾기
• OFFSET 함수 - 행과 열 이동 후 참조구하기
• COLUMN 함수 - 열 번호 구하기
• ROW 함수 - 행 번호 구하기
• CHOOSE 함수 - 값목록에서 원하는 값 선택하기
• MATCH 함수 - 범위에서 값의 위치 찾기
• INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기
• VLOOKUP 함수 - 표에서 값 찾기(간단 버전)
• VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기

• INDEX, MATCH 함수를 이용한 다중조건으로 값찾기

• 인쇄
• 전자우편
• Tweet

• 

이것이 좋아요:

좋아하기 가져오는 중...