엑셀 조건에 맞는 값 모두 가져오기 방법에 관해 알아보는 글입니다. VLOOKUP 함수를 사용할 수 없는 경우 INDEX MATCH 중첩으로 2개 이상 조건에 해당되는 값을 모두 찾아올 수 있습니다. 기본 사용법 및 실무 사용법을 알아볼게요.
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
VLOOKUP 함수 한계
VLOOKUP 함수는 찾을 값이 데이터의 오른쪽에 있는 경우에만 사용할 수 있습니다. 다시 말해서 찾을 값이 데이터의 왼쪽에 있는 경우에는 사용하지 못 합니다.
아래 사진처럼 우리가 찾을 값인 가격이 구매자 데이터의 오른쪽에 있는 경우에만 VLOOKUP 함수를 사용해서 값을 구할 수 있는 것입니다.
즉, 찾을 값이 데이터의 왼쪽에 있는 경우에는 INDEX와 MATCH 함수를 중첩 해서 값을 찾아 와야 합니다.
구매자 및 가격별 과일명 찾아오기
하나의 조건을 만족 하는 값을 INDEX MATCH 함수를 이용해서 찾아와 보겠습니다.
아래 사진에서 김길동 이라는 구매자에 해당되는 과일 명을 INDEX와 MATCH 함수 함수를 이용해서 찾아와볼게요.
아래와 같이 함수식을 입력해 줍니다.
=INDEX($A$2:$A$8,MATCH(E2,$B$2:$B$8,0))
어떻게 해서 위와 같은 함수식이 나오게 되었는지 구체적으로 알아 볼게요.
=INDEX($A$2:$A$8,MATCH(E2,$B$2:$B$8,0))
=INDEX(범위, 행번호)
INDEX 함수 내 인수를 보시면 범위와 행번호로 되어 있습니다. 첫 번째 범위의 경우 우리가 찾을 값인 과일명의 범위(A2:A8)를 선택하면 됩니다.
아래 셀에 자동채우기를 할 때 범위가 바뀌면 안되므로 F4 키를 눌러서 절대참조를 합니다. 그리고 콤마를 입력합니다.
=INDEX($A$2:$A$8,MATCH(E2,$B$2:$B$8,0))
=MATCH(찾을 값, 범위, [일치옵션])
이번에는 INDEX 함수 내 두 번째 인수로 행 번호를 입력 해야 됩니다. MATCH 함수는 우리가 찾을 값과 범위를 입력 하면 행 번호를 알려 주는 함수 입니다.
따라서 INDEX 행 번호 인수 부분에 MATCH 라고 입력을 합니다. 그 후 MATCH 함수 내 첫 번째 인수로 찾을 값을 입력해줘야 하는데요.
우리는 ‘김길동’에 해당되는 값의 행 번호를 찾아야 하므로, 김길동 셀(E2)를 클릭합니다. 그 후 콤마를 입력합니다.
그리고 찾을 값인 ‘김길동’이 포함되어 있는 범위를 선택하면 되는데요. 구매자 열(B2:B8) 범위를 선택합니다. 그 후 F4 키를 눌러서 셀을 고정 시킵니다.
그리고 우리는 정확한 값을 구할 것이므로 MATCH 함수 마지막 인수로 0을 입력하면 됩니다.
엑셀 조건에 맞는 값 모두 가져오기: INDEX MATCH 함수 중첩
위에서는 1개 조건에 해당 되는 값만 찾아와 봤는데요. 이번에는 2개의 조건에 모두 해당 되는 값을 찾아와 보겠습니다. 이번에도 INDEX MATCH 함수를 같이 사용해서 값을 찾아와볼게요.
이번에는 ‘사과’이면서 동시에 ‘김길동’인 가격 값을 찾아와 보겠습니다. 그러기 위해서 아래와 같이 함수식을 입력 합니다.
=INDEX($C$2:$C$8,MATCH(1,(E2=$A$2:$A$8)*(F2=$B$2:$B$8),0))
어떻게 위와 같은 함수식이 나오게 되었는지 자세히 알아 볼게요.
=INDEX($C$2:$C$8,MATCH(1,(E2=$A$2:$A$8)*(F2=$B$2:$B$8),0))
=INDEX(범위, 행번호)
우선 INDEX 함수를 입력한 후 찾을 값인 가격에 해당되는 범위(C2:C8)를 선택합니다. F4 키를 눌러 절대참조를 한후, 콤마를 입력합니다.
=INDEX($C$2:$C$8,MATCH(1,(E2=$A$2:$A$8)*(F2=$B$2:$B$8),0))
그 후 MATCH 함수를 입력합니다. 그 후 찾을 값으로 1을 입력합니다.
여기서 왜 1을 입력해야 하는지 이해가 안 갈 수 있는데요. 아래 내용을 보시면 이해가 될 것입니다.
우리는 ‘사과’에 해당 되면서 동시에 ‘김길동’에 해당되는 값의 가격을 찾아와야 합니다.
과일명 전체 셀 중 ‘사과’에 해당되는 셀이라면 참 값이므로 TRUE 라고 값이 바뀝니다. 그리고 ‘사과’에 해당되지 않는 셀의 경우 거짓 값이므로 FALSE 라고 값이 바뀝니다.
그리고 TRUE는 다시 1로, FALSE는 0으로 바뀝니다. 따라서 아래 사진처럼 값이 바뀌게 됩니다.
2번째 조건도 동일한 방식입니다. 구매자가 ‘김길동’에 해당되는 경우에는 TRUE로, ‘김길동’에 해당되지 않는 경우에는 FALSE로 바뀝니다.
그리고 이 2가지 조건의 값을 곱하면 아래와 같이 결과값이 나오게 됩니다.
- 1 × 1= 1
- 0 × 0= 0
- 0 × 0= 0
- 0 × 0= 0
- 1 × 0= 0
- 0 × 1=0
- 0 × 0= 0
여기서 2가지 조건을 모두 만족하는 경우에만 결과값이 1이라고 나옵니다. 따라서 MATCH 함수 내 찾을 값을 1이라고 입력을 하는 것입니다. 조건 2가지를 모두 만족하는 경우에만 1이라는 값이 나오니까요.
1을 입력했다면 콤마를 입력합니다.
=INDEX($C$2:$C$8,MATCH(1,(E2=$A$2:$A$8)*(F2=$B$2:$B$8),0))
이제 2가지 조건을 입력을 해야 하는데요. 과일명 범위(A2:A8)에서 사과(E2)에 해당되는 값을 찾을 것이므로 괄호 안에 E2=A2:A8 라고 입력이 되도록 합니다.
이 때 아래 셀 자동채우기 시 범위가 바뀌면 안되니까 F4 키를 눌러서 절대참조를 합니다.
참고로 여기서 배열수식이 적용이 되는데요. 배열수식에서 조건을 입력할 때는 반드시 조건 1개 마다 괄호를 입력해줘야 합니다.
첫 번째 조건을 입력한 후 곱하기 기호(*)를 입력하고 두 번째 조건을 입력합니다. 구매자 범위(B2:B8)에서 김길동(F2)에 해당되는 값을 찾을 것이므로 괄호 안에 F2=B2:B8 이라고 입력합니다.
이번에도 F4 키를 눌러서 절대참조를 합니다. 그리고 콤마를 입력합니다.
=INDEX($C$2:$C$8,MATCH(1,(E2=$A$2:$A$8)*(F2=$B$2:$B$8),0))
MATCH 함수 마지막 인수로 정확한 값을 나타내기 위해 0을 입력합니다.
그리고 엔터키를 누르면 2000이라는 값이 찾아와집니다. 아래 셀에도 자동채우기를 하면 됩니다.
특정 범위를 만족하는 점수 찾아오기
이번에는 특정 범위를 만족하는 등급을 INDEX MATCH 함수를 이용해서 찾아와보겠습니다.
예를들어 95점 이상~100점 이하이면 A 등급이 찾아와지도록 하는 식입니다. 첫 번째 셀의 경우 성적이 76점인 경우의 등급을 찾아오는 것입니다.
값을 찾아오기 위해 아래와 같이 함수식을 입력합니다.
=INDEX($C$2:$C$7,MATCH(1,(F2>=$A$2:$A$7)*(F2<=$B$2:$B$7),0))
어떻게 이러한 함수식이 나오는지 자세히 알아볼게요.
=INDEX($C$2:$C$7,MATCH(1,(F2>=$A$2:$A$7)*(F2<=$B$2:$B$7),0))
우선 우리는 등급을 찾을 것이므로 INDEX 함수 내 첫 번째 인수로 등급 범위(C2:C7)을 선택합니다. F4 키를 눌러 절대참조를 해주세요. 그리고 콤마를 입력해주세요.
=INDEX($C$2:$C$7,MATCH(1,(F2>=$A$2:$A$7)*(F2<=$B$2:$B$7),0))
MATCH 함수를 입력하고 찾을 값으로 1을 입력합니다. 76점(F2)이 이상 범위(A2:A7) 및 이하 범위(B2:B7)에 모두 해당되는 값을 찾을 것입니다. 따라서 위와 같이 조건식을 입력합니다.
이때도 반드시 괄호 안에 조건을 1개 씩 입력해야 합니다. 절대참조도 해줘야 하고요. MATCH 마지막 인수로 0을 입력하고 엔터키를 누릅니다.
그럼 아래 사진처럼 각 범위를 만족하는 등급이 바로 찾아와집니다. 아래 셀에도 자동채우기를 해서 값을 완성시킬 수 있습니다.
INDEX MATCH 함수 실무 예제
이번에는 실제 실무에서 사용할 수 있는 예제로 INDEX MATCH 함수를 사용하는 방법입니다.
아래 조건에 해당되는 값을 찾아온다고 해보겠습니다.
- 조건: ‘시군구명’이 ‘창원시 의창구’이고, ‘MMS취득일자’가 ‘2022.7.13’인 ‘학교명’ 찾아오기
우선 시트를 2개 활용할 것인데요. ‘실무예시 데이터’ 시트에서 값을 가져와 보겠습니다. 그리고 ‘값 찾아오기’ 시트에 찾아온 값을 입력해 볼게요.
그러기 위해서 아래와 같이 함수식을 입력할 수 있습니다.
=INDEX(‘실무예시 데이터’!$B$2:$B$368,MATCH(1,(‘실무예시 데이터!$A$2:$A$56=’값 찾아오기’!A2)*(‘실무예시 데이터’!$B$2:$B$56=’값 찾아오기’!B2),0))
우선 INDEX 함수 첫 번째 인수로 범위를 입력해야 하는데요. 학교명에 해당되는 전체 범위를 ‘실무예시 데이터’에서 선택해 줍니다.
그 후 MATCH 함수 내 조건을 2가지 지정해야 하는데요. 조건 범위는 ‘실무예시 데이터’ 시트에서 찾아올 것입니다. 그리고 조건에 해당되는 셀은 ‘값 찾아오기’ 시트에서 선택을 해보겠습니다.
첫 번째로 ‘시군구명’ 전체 범위(A2:A56)에서 ‘창원시 의창구(A2)’ 셀을 찾아라는 조건을 지정합니다.
두 번째로 ‘MMS취득일자’ 전체 범위(B2:B56)에서 ‘2022.7.13(B2)’에 해당되는 셀을 찾아라는 조건을 지정합니다.
이 때도 반드시 범위 부분에는 절대참조를 해야 합니다.
그리고 MATCH 함수 마지막 인수로 0을 입력하고 엔터키를 누릅니다. 그럼 조건 2가지를 모두 만족하는 값이 바로 찾아와집니다.
아래 셀에도 자동채우기를 하면 모든 값을 찾아올 수 있습니다.
이렇게 엑셀 조건에 맞는 값 모두 가져오기 방법을 알아보았습니다.
<결론>
- 엑셀 조건에 맞는 값 모두 가져오기
- 찾을 값이 데이터의 왼쪽에 있는 경우: INDEX MATCH 함수 사용하기
- 2가지 이상 조건을 만족하는 값 모두 가져와야 하는 경우: INDEX MATCH 함수 사용하기
이상 엑셀 조건에 맞는 값 모두 가져오기에 관한 글이었습니다.