엑셀 핵심 함수 · XLOOKUP과 VLOOKUP
Excel Lookup Functions (XLOOKUP, VLOOKUP, INDEX/MATCH)
엑셀 핵심 함수란?
- 조회 함수는 두 표를 기준값 하나로 잇는 도구
- XLOOKUP은 편하지만 버전에 따라 없을 수 있음
- 오류의 대부분은 함수가 아니라 기준값 상태
조회 함수를 익힌다는 말은 문법을 외운다는 뜻이 아닙니다. 인사·교육 담당자의 엑셀 작업은 대부분 두 표를 하나의 기준값으로 잇는 일입니다. 인사 명부의 사번으로 교육 이수 내역을 붙이고, 수강생 명단에 부서와 직급을 채우고, 연봉계약 대상자 표에 평가 등급을 가져옵니다. 이 일을 눈으로 복사해 붙이면 300명 규모에서 반드시 몇 줄이 어긋납니다. 조회 함수는 속도보다 정확성을 위한 도구입니다.
VLOOKUP은 =VLOOKUP(찾을 값, 표 범위, 열 번호, 0) 형태로, 표의 맨 왼쪽 열에서 찾아 오른쪽 몇 번째 열을 가져옵니다. 마지막 인수를 0(FALSE)으로 두지 않으면 비슷한 값을 가져오는 근사 일치로 동작하고, 표 중간에 열을 하나 끼우면 열 번호가 밀려 엉뚱한 값을 냅니다. INDEX/MATCH는 =INDEX(가져올 열, MATCH(찾을 값, 찾을 열, 0)) 형태로 찾을 열과 가져올 열을 따로 지정하므로, 왼쪽 방향 조회가 되고 열 삽입에도 견딥니다.
XLOOKUP은 =XLOOKUP(찾을 값, 찾을 범위, 가져올 범위, 없을 때 표시할 값) 형태입니다. 기본값이 정확히 일치이고 방향 제한이 없으며, 못 찾았을 때의 문구를 함수 안에서 지정합니다. 다만 Microsoft 365와 비교적 최근 버전에서만 동작하므로 파일을 받는 사람의 엑셀 버전을 먼저 확인해야 합니다. 구버전에서 열면 수식이 오류로 바뀝니다. 여러 회사와 파일을 주고받는다면 INDEX/MATCH가 안전한 공통분모입니다.
현장에서 가장 자주 어긋나는 지점은 함수가 아니라 기준값의 상태입니다. 사번이 한쪽은 숫자, 다른 쪽은 문자로 저장돼 있거나 이름 뒤에 공백이 붙어 있으면 어떤 함수든 '없음'을 돌려줍니다. 이름을 기준값으로 쓰면 동명이인 가운데 첫 번째 사람의 값만 가져오고도 오류 표시가 뜨지 않습니다. 기준값은 사번처럼 중복 없는 값으로 잡고, 결과의 오류 건수와 전체 건수를 원본과 대조하는 습관이 함수 선택보다 중요합니다.
| 구분 | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| 기본 형태 | =VLOOKUP(찾을 값, 표 범위, 열 번호, 0) | =INDEX(가져올 열, MATCH(찾을 값, 찾을 열, 0)) | =XLOOKUP(찾을 값, 찾을 범위, 가져올 범위) |
| 조회 방향 | 찾을 열의 오른쪽만 | 제한 없음 | 제한 없음 |
| 일치 방식 기본값 | 근사 일치(0을 넣어야 정확히 일치) | MATCH의 0으로 정확히 일치 지정 | 정확히 일치 |
| 열 삽입 시 | 열 번호가 밀려 틀어질 수 있음 | 영향 적음 | 영향 적음 |
| 못 찾았을 때 | 오류 표시(IFERROR로 감싸야 함) | 오류 표시(IFERROR로 감싸야 함) | 네 번째 인수로 문구 지정 |
| 쓸 수 있는 버전 | 거의 모든 버전 | 거의 모든 버전 | Microsoft 365 등 최근 버전 |
VLOOKUP은 스프레드시트 초기부터 쓰여 온 조회 함수이고, 그 한계를 피하려는 실무 관행으로 INDEX와 MATCH의 조합이 널리 퍼졌습니다. XLOOKUP은 이 둘의 불편을 줄이려고 마이크로소프트가 뒤에 추가한 함수여서 엑셀 버전에 따라 쓸 수 없는 경우가 있습니다.
가상의 사례로 살펴보겠습니다. 임직원 680명의 식품 제조사 A사 교육팀은 연말 교육 실적 보고를 앞두고 인사 명부와 LMS 이수 내역 2,300건을 이어 붙여야 했습니다. 지난해에는 대리 한 명이 이틀 동안 복사해 붙였고, 보고 뒤 부서별 이수율 3곳이 틀린 것으로 드러났습니다.
- 교육팀 대리가 이수 내역의 이름으로 부서를 조회했더니 동명이인 11명이 모두 첫 번째 사람의 부서로 채워졌습니다. 팀장이 "이름 말고 사번으로 다시 잡읍시다"라고 했습니다.
- 사번으로 바꾸자 이번에는 140건이 '없음'으로 나왔습니다. LMS에서 내려받은 사번이 문자 형식으로 저장돼 있었고, 숫자로 변환하자 오류가 6건으로 줄었습니다.
- 남은 6건은 연중 퇴사자였습니다. 대리는 XLOOKUP의 네 번째 인수에 "퇴사자 확인"을 넣어 빈칸 대신 사유가 보이도록 바꿨습니다.
- 계열사 인사팀에 파일을 보내자 "수식이 전부 오류로 뜹니다"라는 답이 왔습니다. 그쪽 엑셀이 구버전이어서 INDEX/MATCH로 수식을 다시 짜 보냈습니다.
- 팀장은 조회 결과 건수와 원본 건수를 맞춰 보는 검산 칸을 시트 맨 위에 두게 했고, 2,300건이 양쪽에서 일치하는 것을 확인한 뒤 보고서로 넘겼습니다.
- 집계 시간은 이틀에서 3시간으로 줄었고, 보고 뒤 정정 요청은 3건에서 0건이 됐습니다. 같은 파일은 다음 분기 실적 집계에도 그대로 쓰였습니다.