“오피스 툴만 제대로 다뤄도 퇴근 시간이 빨라진다.”
사무직 직장인들은 종일 문서의 늪 속에서 전쟁을 치르고 있죠. 그래서 위와 같은 문구에 쉽게 공감할 수 있을 텐데요. 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 함수를 중첩해 값을 구해볼 수 있다는 것을요! 😀
마지막으로 유재석 부장의 상여금 560셀의 오른쪽 아래를 잡고 드래그하니 모든 멤버들의 상여금이 도출됐습니다! 모든 데이터 범위에 $을 넣어주는 것, 꼭 잊지 마세요!
다섯 멤버들의 상여금 값을 구하기 위해 많은 과정을 거쳐왔네요. 누군가는 ‘그냥 상여금 기준표에서 잘 찾아보면 더 쉬운 것 아냐?’라고 할 수도 있습니다. 하지만 상여금 대상자가 다섯 명이 아닌 백 명, 천 명, 만 명이 넘어간다면 감당하기가 힘들겠죠. 그래서 우리는 이제까지 검색함수를 배워온 것입니다. 😀 그럼 다음 직장인을 위한 Office Tip도 기대해주세요~
[직장인을 위한 Office Tip] 엑셀 함수 정리 ① – 기본, COUNT 함수
[직장인을 위한 Office Tip] 엑셀 함수 정리 ② – 논리 함수 IF, OR, AND
