[직장인을 위한 Office Tip] 엑셀 함수 정리 ⑤ – 검색 함수 MATCH, INDEX

 

“오피스 툴만 제대로 다뤄도 퇴근 시간이 빨라진다.”

 

사무직 직장인들은 종일 문서의 늪 속에서 전쟁을 치르고 있죠. 그래서 위와 같은 문구에 쉽게 공감할 수 있을 텐데요. LG디스플레이 기업 블로그에서는 직장인들을 위한 Office 팁을 소개해드리고 있습니다.

 

지난번 VLOOKUP 검색 함수를 소개해 드렸었는데요. VLOOKUP 함수를 사용할 때 검색 대상의 값은 시트의 가장 첫 번째, 즉 A행에 있어야 한다고 말씀 드렸죠? 하지만 함수에 맞춰 일일이 표를 편집하기도 여간 귀찮은 일이 아닙니다. 귀차니즘에 빠진 분들에게 INDEX와 MATCH 함수를 중첩해 같은 값을 끌어낼 방법을 소개해 드리고자 합니다.

 

 

필요한 값을 콕콕! 엑셀 MATCH, INDEX 함수

 

적을 알고 나를 알면 백전백승! 먼저 함수의 개념을 하나씩 알아보겠습니다.

 

함수명설명

MATCH	데이터 범위 내에서 원하는 데이터의 행과 열 위치를 구해주는 함수
INDEX	데이터 범위 내에서 행과 열 위치를 참조해 특정 위치의 값을 구해주는 함수

MATCH 함수는 게임 할 때의 좌표 개념을 대입해보면 조금 더 이해가 쉬울 텐데요. 같은 편에게 SOS를 청할 때 ‘2시 방향으로 와줘!’ 혹은 더욱 정확하게 게임에 쓰여 있는 좌표인 ‘(21, 343)로 와줘!’ 하며 도움을 청하곤 하잖아요. 그래야 같은 편 친구가 정확하게 그 위치로 찾아와 구해줄 수 있으니까요. 이와 비슷하게 MATCH 함수는 내가 구하고 싶은 데이터가 데이터 범위 내에서 몇 번째 열, 몇 번째 행에 있는지 숫자로 표기해줍니다.

 

INDEX 함수는 이와 반대로 좌표가 주어져 있으면 데이터 범위에서 특정 위치의 값을 콕! 찾아주는 역할을 하지요. 서로 상호보완적인 관계라 할 수 있겠죠? 아래 예시를 보며 천천히 설명해보겠습니다.

 

 

1) 첫 번째 미션: MATCH 함수로 유재석 부장의 상여금 ‘좌표’를 구해라!

 

먼저 MATCH 함수를 이용해 아래 표에서 직급과 호봉 위치를 구해보겠습니다. 좌푯값을 구하기 위해 행 위치, 열 위치로 표를 분리했습니다. 원래 INDEX 함수 식에서 바로 중첩해 엑셀 식을 세우지만, 쉬운 이해를 위해 이렇게 따로 표를 만들었답니다.

 

 

무한상사에선 직급, 호봉마다 상여급을 다르게 지급하고 있는데요. 예를 들어 유재석은 ‘부장-4호봉’ 상여급을 가져가게 됩니다. 자, 이제부터 본격적으로 MATCH 값을 구해보겠습니다.

 

MATCH 함수 인수는 해당 값, 데이터 범위, 정확도입니다.

 

 

우리가 구해야 할 것은 유재석 부장의 상여금 좌표! 먼저 행 값부터 구해보죠. MATCH 함수 해당 값에 유재석 부장의 ‘직급’인 부장 셀 값(B4)을 넣어주고 다음으론 데이터 범위($A$13:$A$17)를 쭉 드래그해줍니다. 마지막으로 오차 없이 정확한 값을 구해달라는 의미로 0을 넣어줍니다. 여기까지 하면 1이 도출됩니다!

실제로 상여금 기준표에서 부장 직급은 1행에 있네요. 잘 구해진 것 같습니다.

 

여기서 잠깐! 범위에 써진 $A$13:$A$17에서 ‘$(달러)’의 의미는 무엇인가요?

 

처음 보는 사람들은 당황할 수밖에 없는 이 기호! 뭘까요? 엑셀에서 $를 행, 열 번호에 모두 붙일 경우,  ‘절대 참조’의 의미가 됩니다. ‘내 데이터 범위는 이걸로 고정이야. 절대 변하지 않아!’일 경우, 이 달러 표시를 붙여주죠. 특히 엑셀에선 셀의 오른쪽 밑을 드래그해 위 식을 참조하는 경우가 많은데요. 그럴 경우 $는 지정한 데이터 범위가 바뀌지 않도록 꼭! 잡아주는 역할을 한답니다. 셀을 선택한 뒤 키보드 위에 있는 ‘F4’를 눌러주면 간단하게 절대 참조를 지정할 수 있습니다.

 

 

 

열 위치 도출 방법도 같습니다. MATCH 함수를 입력한 뒤 4호봉(C4)을 클릭해주고, 호봉에 해당하는 데이터 범위($B$13:$E$13)를 긁은 후 0을 입력해주면… 짠! 4열이라고 나오네요. 결국, 유재석 부장 상여금 위치는 (1, 4)가 됩니다. 기준표에서 찾아봐도 딱 맞네요.

 

 

2) 두 번째 미션: INDEX 함수로 유재석 부장의 상여금을 확실히 구해라!

 

이제 MATCH 함수와 INDEX 함수를 함께 이용해 상여금을 계산해보겠습니다. 앞서 설명드린 MATCH 함수만 확실히 이해하셨다면, 술술~ 답을 구해낼 수 있습니다.

 

INDEX 함수 인수는 데이터 범위, 행 번호, 열 번호입니다. 행 번호와 열 번호는 앞선 MATCH 단계에서 이미 구하는 법을 익혔었죠? 그러니 데이터 범위만 잘 입력해주면 됩니다.

 

 

=INDEX를 입력한 후 상여금 기준표에서 값이 적혀있는 범위($B$14:$E$18)를 넣어줍니다. 다음엔 앞서 구했던 행 값에 대한 MATCH 함수(MATCH(B4,$A$14:$A$18,0))를 넣어주고 마지막으로 열 값에 대한 MATCH 함수(MATCH(C4,$B$13:$E$13,0))를 넣어주면 상여금 560이 도출됩니다.

 

혹시 눈치 채셨나요? 앞서 구했던 행, 열 위치에 대한 표가 없어도 이곳에서 바로 MATCH 함수와 INDEX 함수를 중첩해 값을 구해볼 수 있다는 것을요! :D

 

 

 

마지막으로 유재석 부장의 상여금 560셀의 오른쪽 아래를 잡고 드래그하니 모든 멤버들의 상여금이 도출됐습니다! 모든 데이터 범위에 $을 넣어주는 것, 꼭 잊지 마세요!

---

다섯 멤버들의 상여금 값을 구하기 위해 많은 과정을 거쳐왔네요. 누군가는 ‘그냥 상여금 기준표에서 잘 찾아보면 더 쉬운 것 아냐?’라고 할 수도 있습니다. 하지만 상여금 대상자가 다섯 명이 아닌 백 명, 천 명, 만 명이 넘어간다면 감당하기가 힘들겠죠. 그래서 우리는 이제까지 검색함수를 배워온 것입니다. :D 그럼 다음 직장인을 위한 Office Tip도 기대해주세요~