엑셀 : Index, Match 함수로 다중 조건 검색하기

 

---

 

엑셀을 사용하다 보면 많은 조건을 모두 고려하여 값을 찾아내야 하는 상황이 있습니다.

아래와 같은 상황에서 값을 어떻게 찾아낼 수 있는지, 함수 사용 시 꿀팁 전달 드립니다.

 

 

정답부터 말씀 드리자면 위의 표에서 Low, 2, A에 모두 해당하는 값을 찾기 위한 함수는 다음과 같습니다.

 

 

=INDEX($E$2:$E$13,(MATCH(1,($B$2:$B$13=$B$16)*($C$2:$C$13=$C$16)*($D$2:$D$13=$D$16),0)),1)

 

*알파벳과 숫자 앞에 붙어있는 "$"는 셀을 고정하는 함수이므로 있거나 없거나 크게 상관이 없습니다.

원리가 궁금하시면 아래 함수 설명을 보시면 되고, 건너뛰어서 오류 해결하는 방법으로 가셔도 상관 업습니다.

 

 

 

INDEX 함수

---

 

 

Index 함수는 기준이 되는 값(들)을 설정하고 열과 행을 지정해서 해당 위치의 값을 불러오는 역할을 합니다.

• Reference : 실제로 불러올 값을 찾는 영역
• Row_num : 값이 위치한 행번호
• Column_num : 값이 위치한 열번호 (생략가능)
• Area_num : row_num과 column_num이 교차하는 셀의 반환할 참조 번위를 선택 (생략가능)

(Reference에 A1:B4,D1:E4,G1:H4) 셀을 나타내면, area_num 1은 A1:B4, 2는 D1:E4, 3은 G1:H4 범위가 된다.)

-> 현재는 크게 쓸모는 없지만, 하기와 같이 추가 조건이 생기거나 아예 다른 표에서 참조를 해야 하는 경우에 활용해 볼 수 있습니다.

 

=INDEX(($E$32:$E$43,F32:F43,G32:G43),(MATCH(1,($B$32:$B$43=$B$16)*($C$32:$C$43=$C$16)*($D$32:$D$43=$D$16),0)),1,L41)

* 추가조건 L41 셀

 

 

 

Match 함수

 

---

 

Index 함수가 Reference를 기준으로 행, 열 위치를 설정해서 값을 불러오는 기능을 담당한다면 Match 함수는 우리가 설정한 조건과 맞는지 확인해 주는 기능을 수행합니다.

• lookup_value : 찾을 값
• lookup_array : 값을 어디서 찾을 것 인가?
• [match_type] : 인수에 따라 매치 타입 설정(생략가능)

(1 또는 생략 : lookup_value보다 작거나 같은 값 중 최댓값 출력 / 0 : value와 같은 첫째 값 출력 / -1 : value보다 크거나 같은 값 중 최솟값 출력)

 

Index 함수의 메커니즘을 이해했으면 알겠지만 Match 함수로 찾는 값은 "상대좌표"이므로 Index에서 어떤 행과 열로 찾아들어가는지를 설정해 주는 함수이다.

 

 

 

Index+Match 함수

 

---

이제 처음으로 돌아가서 최종 함수를 보자.

 

 

Match의 첫 번째에는 찾을 값이 들어가야 하는데, 엉뚱하게 "1"이 들어가 있고 값을 찾을 범위에는 "A=B" 형식으로 들어가 있는 걸 알 수 있다.

 

이유는, 엑셀에서는 (참=1=True) / (거짓=0=False)으로 표현하는데 lookup_array에서 참 거짓 판별식을 넣고 나오는 값이 참일 때를 찾는 형식으로 Match 함수를 사용한 것이다.

 

따라서 다중 조건을 설정할 때 (A=B)*(C=D)*(E=F)가 모두 참이라면 (1)*(1)*(1)=1, 하나라도 거짓이라면 (1)*(1)*(0)=0이 나온다. 따라서 잘못된 조건을 설정하면 오류가 날 것이고, 조건(x=y)을 n개 설정하더라도 결국 모두 참인 조건을 찾을 수 있다면 해당 좌표를 출력할 수 있다.

 

 

 

 

오류 해결법

 

---

 

모든 과정을 똑같이 따라 했다면 이 질문이 무조건 나와야 한다.

"#N/A"가 나오는데요?

출처 입력

#N/A가 나왔다면 높은 확률로 Match 함수 메커니즘을 사용함으로써 나오는 오류이다.

Match 함수는 설명했다시피 한 개의 조건이라도 False=0=거짓이 나오면 오류가 나오기 때문에 "#N/A"를 출력한다. 이유는 셀이 병합되는 과정에서 첫 번째 행을 제외한 나머지 행은 모두 빈 셀로 바꾸기 때문에 일어나는 문제이다.

 

 

아마 병합을 풀어보면 이렇게 나오게 될 것이고, 따라서 값을 못 찾고 오류가 나는 것이다.

해당 오류를 해결하기 위해서는 모든 셀에 값을 넣어줘야 하는데 방법은 아래와 같다.

 

 

사진 삭제

사진 설명을 입력하세요.

 

1) 빈 셀이 포함된 셀 전체를 드래그한다.

 

사진 삭제

사진 설명을 입력하세요.

2) "Ctrl + G"를 눌러서 이동 옵션을 실행한다.

3) "옵션(S)"에 들어간다.

 

사진 삭제

사진 설명을 입력하세요.

4) "빈셀(K)"를 체크한 뒤 확인을 누른다.

 

 

사진 삭제

사진 설명을 입력하세요.

5) 그러면 위와 같이 변하는데 해당 상태에서 키보드 "=", 키보드"화살표 위(↑)", 키보드 "Ctrl+Enter"를 누른다.

 

사진 삭제

사진 설명을 입력하세요.

6) 그러면 이렇게 셀들이 모두 채워지고 "#N/A" 값에 원하는 검색 값이 나오는 것을 확인할 수 있다.

 

 

하지만, 이렇게 내버려두면 표가 지저분하기 때문에 정리를 해야 하는데, 그냥 셀 병합을 하면 다시 첫번째 행을 제외하고는 빈셀이 되어버립니다. 값일 채운 상태에서 셀 병합을 해야하는데 방법은 아래와 같습니다.

 

 

셀 값 안 지우고 병합하는 방법

 

---

 

 

 

1) 병합할 행과 동일한 크기의 빈 셀을 찾는다.

 

2) 빈 셀을 병합한다.

3) 빈 셀을 서식복사 한 뒤 병합을 원하는 셀들의 첫 번째 셀을 클릭한다.

4) 이렇게 병합하면 내부 셀의 값들은 그대로 남고, 병합할 수 있습니다.

 

하지만, 색이나 글꼴 등 기존 서식은 다시 설정해줘야 하고, 이렇게 하나씩 다 바꿔주기가 힘들 거라고 생각합니다.

따라서 한 번에 서식도 남기고 진행하는 방법을 알려드리겠습니다.

 

 

1) 빈 셀을 채우기 전 맨 처음 단계에서 병합된 셀을 모두 복사합니다.

2) 옆에 사용하지 않는 빈 셀에 붙여 넣습니다.

3) 위에 설명한 빈 셀을 채우는 과정을 모두 진행한 뒤 붙여 넣어 놓은 셀을 한 줄씩 드래그 후 서식 복사를 진행합니다.

 

4) 이 과정은 꼭 한 줄씩 진행하여야 하며 이렇게 하면 서식도 깨지지 않고 셀의 값도 유지할 수 있습니다.

 

설명이 약간 난해한데 동영상을 첨부하여 설명드리겠습니다.

 

 

 

저는 회사에서 대량의 측정값을 다루거나, 기술 문서 조건에 맞는 값을 정리하는 일을 많이 하는데 해당 방법으로 회사에서 업무 속도를 엄청 늘리고, 한눈에 보기 쉽게 정리할 수 있는 양식을 만들었습니다.

 

여러분도 혹시 눈알 빠지게 비교하면서 데이터를 찾거나, 너무 많은 데이터에 치여서 실수를 많이 한다면 엑셀 함수를 이용해서 스마트하게 업무 하세요.