핵심 요약
- 고객 이름을 넣으면 연락처가 나오게 하려면
=XLOOKUP(찾을 이름,이름 범위,연락처 범위,"명단에 없음")한 줄이면 됩니다. - XLOOKUP은 Microsoft 365, Excel 2021, Excel 2024에서 쓸 수 있고 Excel 2016·2019에는 없습니다.
- 모든 버전에서 같은 결과를 내려면
=IFERROR(INDEX(연락처 범위,MATCH(찾을 이름,이름 범위,0)),"명단에 없음")을 씁니다.
고객 명단에서 이름 하나로 연락처나 고객번호를 찾아 옆 칸에 채우는 일은 영업 자료를 만들 때 가장 자주 하는 작업 중 하나입니다. 오래 엑셀을 써 온 분들은 VLOOKUP이나 INDEX와 MATCH를 겹친 수식을 많이 씁니다. 특히 INDEX·MATCH는 찾을 열의 위치에 상관없이 값을 가져올 수 있어서 실무에서 즐겨 쓰는 조합입니다.
XLOOKUP은 이 INDEX·MATCH가 하던 일을 함수 하나로 해 줍니다. 찾는 값이 없을 때 보여 줄 글자까지 수식 안에 바로 적을 수 있습니다. 엑셀 공부를 하는 분이라면 "새 함수와 옛 조합이 같은 결과를 낸다"는 점을 비교해 보는 것만으로도 찾기 함수의 원리가 잘 잡힙니다. 목적별 함수 조합 시리즈의 한 편으로, 같은 표에서 두 방법을 나란히 써 봅니다.
예제 표 — 고객 명단과 찾을 이름
A~D열에 고객 명단이 있고, F2 셀에 찾을 이름을 적습니다. 이름·번호는 모두 가상입니다.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | 고객번호 | 이름 | 지역 | 연락처 | 찾을 이름 | 연락처 | 고객번호 | |
| 2 | C1001 | 김민준 | 서울 | 010-1234-5678 | 박지호 | |||
| 3 | C1002 | 이서연 | 부산 | 010-5678-1234 | ||||
| 4 | C1003 | 박지호 | 대구 | 010-2345-6789 | ||||
| 5 | C1004 | 최수아 | 인천 | 010-6789-2345 | ||||
| 6 | C1005 | 정하윤 | 광주 | 010-7890-3456 | ||||
| 7 | C1006 | 강도윤 | 대전 | 010-4567-8901 | ||||
| 8 | C1007 | 윤지우 | 울산 | 010-8901-4567 | ||||
| 9 | C1008 | 한지민 | 수원 | 010-3456-7890 |
목표는 두 가지입니다. G2에 박지호의 연락처를, H2에 박지호의 고객번호를 가져옵니다. 고객번호(A열)는 이름(B열)보다 왼쪽에 있다는 점을 기억해 두세요.
완성 수식 — G2와 H2에 한 줄씩
G2 셀(연락처):
=XLOOKUP(F2,B2:B9,D2:D9,"명단에 없음")H2 셀(고객번호):
=XLOOKUP(F2,B2:B9,A2:A9,"명단에 없음")| F2에 넣은 이름 | G2 결과 | H2 결과 |
|---|---|---|
| 박지호 | 010-2345-6789 | C1003 |
| 홍길동(명단에 없음) | 명단에 없음 | 명단에 없음 |
F2의 이름만 바꾸면 G2·H2가 곧바로 새 사람의 정보로 바뀝니다. 명단 옆에 여러 사람을 한꺼번에 찾으려면 범위를 $B$2:$B$9처럼 절대 참조로 고정한 뒤 아래로 채웁니다.
수식 풀어 보기 — 인수 네 개를 차례로
| 순서 | 인수 | G2 수식에서 | 하는 일 |
|---|---|---|---|
| 1 | 찾을 값(lookup_value) | F2 | "박지호" |
| 2 | 찾을 범위(lookup_array) | B2:B9 | 이름 열에서 박지호를 찾습니다. 위에서 3번째입니다 |
| 3 | 가져올 범위(return_array) | D2:D9 | 연락처 열의 3번째 값 010-2345-6789를 돌려줍니다 |
| 4 | 없을 때 표시(if_not_found) | "명단에 없음" | 이름을 못 찾으면 이 글자를 보여 줍니다 |
핵심은 2번과 3번입니다. 찾을 범위에서 "몇 번째인지"를 알아내고, 가져올 범위에서 "같은 번째"의 값을 꺼냅니다. 가져올 범위가 찾을 범위의 오른쪽이든 왼쪽이든 상관없기 때문에 H2처럼 왼쪽 열의 고객번호도 가져올 수 있습니다. VLOOKUP은 찾는 열이 표의 첫 열이어야 해서 이 일을 하지 못합니다.
Microsoft 문서 기준으로 XLOOKUP은 따로 지정하지 않으면 정확히 일치하는 값을 찾습니다. VLOOKUP에서 자주 빠뜨리던 FALSE를 신경 쓰지 않아도 됩니다. 네 번째 인수를 생략하면 못 찾았을 때 #N/A가 나옵니다.
INDEX·MATCH로 풀면 같은 두 단계입니다
=INDEX($D$2:$D$9,MATCH(F2,$B$2:$B$9,0))- 안쪽
MATCH(F2,$B$2:$B$9,0)이 이름 열에서 박지호의 위치 3을 구합니다. 마지막 0이 "정확히 일치"라는 뜻입니다. - 바깥
INDEX($D$2:$D$9,3)이 연락처 열의 3번째 값 010-2345-6789를 꺼냅니다.
XLOOKUP의 2번·3번 인수가 하던 일을 MATCH와 INDEX가 나눠서 하는 셈입니다. 고객번호가 필요하면 INDEX의 범위만 $A$2:$A$9로 바꾸면 되고, 결과는 똑같이 C1003입니다.
한 번에 여러 칸 가져오기
가져올 범위에 열을 여러 개 넣으면 결과가 오른쪽으로 펼쳐집니다. H2의 고객번호 수식을 지우고 G2에 아래 수식을 넣으면 G2에 지역, H2에 연락처가 함께 나옵니다.
=XLOOKUP(F2,B2:B9,C2:D9)이 동작은 Microsoft 문서의 예제(=XLOOKUP(B2,B5:B14,C5:D14,…))를 기준으로 설명한 것입니다. 결과가 펼쳐질 오른쪽 칸이 비어 있어야 하며, 이미 값이 있으면 #SPILL! 오류가 납니다. 또 Microsoft 문서에 따르면 이렇게 펼쳐지는 수식은 엑셀 표(표 기능으로 만든 범위) 안에서는 지원되지 않습니다.
자주 막히는 곳
버전부터 확인하세요. Microsoft 문서는 "XLOOKUP은 Excel 2016 및 Excel 2019에서 사용할 수 없습니다"라고 안내합니다. 내 PC는 Microsoft 365라도 파일을 받는 사람이 2016·2019를 쓰면 그쪽에서는 제대로 계산되지 않습니다. 파일을 여러 사람과 주고받는다면 INDEX·MATCH가 안전합니다.
- 분명히 있는 이름인데 "명단에 없음" — 이름 뒤에 공백이 붙어 있는 경우가 흔합니다. "박지호 "는 "박지호"와 다른 값으로 취급돼 못 찾습니다.
=XLOOKUP(TRIM(F2),B2:B9,D2:D9)처럼 찾을 값을 TRIM으로 감싸거나, 명단 쪽 공백을 먼저 정리합니다. - 숫자와 텍스트가 섞인 코드 — 고객번호가 1003처럼 숫자만으로 되어 있을 때, 명단에는 텍스트 "1003"으로, 찾는 칸에는 숫자 1003으로 들어가 있으면 서로 다른 값이라 찾지 못합니다. 양쪽 형식을 맞춥니다.
- 아래로 채웠더니 뒤쪽 결과가 틀린다 — 범위에
$를 붙이지 않아 한 줄 내려갈 때마다 범위도 같이 내려간 경우입니다.$B$2:$B$9처럼 고정합니다. - 찾을 범위와 가져올 범위의 행 수 — B2:B9와 D2:D9처럼 시작 행과 끝 행을 똑같이 맞춰야 "같은 번째" 값이 나옵니다.
다른 방법 — 버전별로 고르기
| 방법 | 쓸 수 있는 버전 | 왼쪽 열 가져오기 | 못 찾았을 때 |
|---|---|---|---|
XLOOKUP | Microsoft 365, Excel 2021·2024 | 됨 | 넷째 인수로 지정 |
INDEX+MATCH | 모든 버전 | 됨 | IFERROR로 감싸기 |
VLOOKUP | 모든 버전 | 안 됨 | IFERROR로 감싸기 |
INDEX·MATCH에서 "명단에 없음"을 보여 주려면 IFERROR로 감쌉니다.
=IFERROR(INDEX($D$2:$D$9,MATCH(F2,$B$2:$B$9,0)),"명단에 없음")다만 IFERROR는 못 찾은 경우뿐 아니라 범위를 잘못 적은 실수까지 모두 "명단에 없음"으로 가려 버립니다. 수식을 처음 만들 때는 IFERROR 없이 결과를 확인한 뒤 마지막에 감싸는 편이 좋습니다.
시험을 준비한다면 VLOOKUP·INDEX·MATCH는 기본으로 익히고, XLOOKUP이 응시하는 급수의 출제 범위에 들어 있는지는 대한상공회의소가 공개하는 출제기준에서 확인하세요.
자주 묻는 질문
같은 이름이 두 명 있으면 누가 나오나요?
XLOOKUP과 MATCH 모두 위에서부터 찾아 처음 만난 사람의 값을 돌려줍니다. XLOOKUP은 여섯 번째 인수(search_mode)에 -1을 넣으면 아래에서부터 찾아 마지막 사람의 값을 돌려줍니다. 동명이인이 있을 수 있는 명단이라면 이름 대신 고객번호처럼 겹치지 않는 값으로 찾는 편이 안전합니다.
이름 일부만 알 때도 찾을 수 있나요?
XLOOKUP의 다섯 번째 인수(match_mode)를 2로 하면 와일드카드를 쓸 수 있습니다. 예를 들어 =XLOOKUP("최*",B2:B9,B2:B9,"없음",2)는 "최"로 시작하는 첫 이름인 최수아를 돌려줍니다. MATCH도 마지막 인수가 0이면 와일드카드를 쓸 수 있습니다.
XLOOKUP과 INDEX·MATCH 중 무엇을 써야 하나요?
혼자 Microsoft 365나 Excel 2021 이후 버전만 쓴다면 XLOOKUP이 짧고 읽기 쉽습니다. 파일을 여러 사람과 주고받거나 Excel 2016·2019를 쓰는 사람이 있다면 INDEX·MATCH가 어느 버전에서나 같은 결과를 냅니다.
함께 보면 좋은 글: 엑셀 INDEX MATCH 함수 사용법 총정리, 엑셀 VLOOKUP 함수 사용법 총정리, 엑셀 절대참조 상대참조 차이와 $ 사용법 · 전체 목록은 엑셀 가이드 목차에서 볼 수 있습니다.
참고: XLOOKUP 함수 (Microsoft 지원), MATCH 함수 (Microsoft 지원), #SPILL! 오류 수정 (Microsoft 지원)
댓글
댓글 남기기