핵심 요약
- 드롭다운에서 고객 이름을 고르면 연락처·생년월일·지역·최근 상담일·만 나이가 카드 한 장에 채워지는 조회 화면을 만듭니다.
- 조합은 데이터 유효성 검사(드롭다운) +
MATCH로 위치 한 번 찾기 +INDEX로 항목별 꺼내기 +IFERROR로 빈칸 처리입니다. - 이서연 님을 고르면
010-0000-7315, 1985-09-24, 경기 수원, 2026-08-14, 만 41세가 한 번에 나옵니다.
상담 전화를 받거나 약속 장소로 가기 전에 고객 한 사람의 정보를 빨리 보고 싶을 때가 있습니다. 명단이 길면 필터를 걸거나 스크롤을 내려 그 사람의 행을 찾아야 하고, 열이 많으면 옆으로 한참 밀어 봐야 합니다. 영업하는 분이라면 이름 하나만 고르면 필요한 정보가 카드처럼 모여 보이는 시트를 하나 만들어 두면 상담 준비가 한결 가벼워집니다.
자격증 시험에서도 목록에서 값을 고르게 하는 데이터 유효성 검사와, 그 값으로 다른 표를 찾아오는 함수는 따로따로 자주 나옵니다. 이 글은 "목적별 함수 조합" 시리즈의 한 편으로, 그 둘을 합쳐 작은 조회 화면을 완성해 봅니다. INDEX·MATCH의 기본 문법은 엑셀 INDEX MATCH 함수 사용법 총정리에 있습니다.
예제 표 — 고객명단 시트와 조회 시트
먼저 고객명단 시트에 아래처럼 명단이 있다고 하겠습니다. 이름·번호·날짜는 모두 가상입니다.
| 고객명단 | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | 이름 | 연락처 | 생년월일 | 지역 | 최근 상담일 |
| 2 | 김민준 | 010-2841-0000 | 1990-03-15 | 서울 마포 | 2026-09-02 |
| 3 | 이서연 | 010-0000-7315 | 1985-09-24 | 경기 수원 | 2026-08-14 |
| 4 | 박지훈 | 010-5520-0000 | 1985-09-25 | 인천 연수 | 2026-09-18 |
| 5 | 최수아 | 010-0000-1947 | 1978-12-01 | 서울 송파 | 2026-06-30 |
| 6 | 정우진 | 010-7713-0000 | 1972-01-10 | 경기 고양 | 2026-09-21 |
| 7 | 강하은 | 010-0000-6208 | 1999-11-30 | 서울 강서 | 2026-07-07 |
| 8 | 윤도현 | 010-3196-0000 | 1963-07-07 | 경기 성남 | 2026-09-10 |
| 9 | 한지아 | 010-0000-4482 | 1994-02-14 | 인천 부평 | 2026-05-20 |
새 시트를 하나 만들어 이름을 조회로 바꾸고, 카드 모양의 틀을 적어 둡니다. B2가 이름을 고르는 칸, D2는 명단에서 몇 번째인지 계산해 둘 칸입니다.
| 조회 | A | B | C | D |
|---|---|---|---|---|
| 2 | 이름 | (드롭다운) | 명단 위치 | (수식) |
| 4 | 연락처 | (수식) | ||
| 5 | 생년월일 | (수식) | ||
| 6 | 지역 | (수식) | ||
| 7 | 최근 상담일 | (수식) | ||
| 8 | 만 나이 | (수식) | ||
| 9 | 상담 후 지난 날 | (수식) |
완성 — 드롭다운 하나, 위치 수식 하나, 꺼내는 수식 여러 개
1단계: B2에 이름 드롭다운 만들기
- 조회 시트의 B2를 선택합니다.
- 데이터 탭의 데이터 도구 그룹에서 데이터 유효성 검사를 누릅니다.
- 설정 탭의 제한 대상 상자에서 목록을 고릅니다. Microsoft 문서에 따라 이 상자를 허용이라고 부르기도 하니, 버전에 따라 이름이 조금 다를 수 있습니다.
- 원본 상자를 클릭한 뒤 고객명단 시트로 가서 A2:A9를 선택합니다. 원본 상자에 시트 이름과 범위가 들어갑니다. 직접 입력한다면
='고객명단'!$A$2:$A$9입니다. - 드롭다운 표시 확인란이 켜져 있는지 보고 확인을 누릅니다.
B2를 클릭하면 오른쪽에 화살표가 생기고, 누르면 여덟 명의 이름이 나옵니다. 이 단계는 화면에서 하는 기능이라 수식 계산으로 검증할 수 없어 Microsoft 문서의 절차를 기준으로 적었습니다.
2단계: D2에 "명단에서 몇 번째인지" 한 번만 찾기
D2: =IFERROR(MATCH(B2,'고객명단'!$A$2:$A$9,0),"")3단계: 항목마다 INDEX로 꺼내기
B4: =IF($D$2="","",INDEX('고객명단'!$B$2:$B$9,$D$2))
B5: =IF($D$2="","",INDEX('고객명단'!$C$2:$C$9,$D$2))
B6: =IF($D$2="","",INDEX('고객명단'!$D$2:$D$9,$D$2))
B7: =IF($D$2="","",INDEX('고객명단'!$E$2:$E$9,$D$2))
B8: =IF(B5="","",DATEDIF(B5,TODAY(),"Y"))
B9: =IF(B7="","",TODAY()-B7)B5와 B7은 표시 형식을 간단한 날짜로, B8과 B9는 일반으로 바꿉니다. 날짜끼리 뺀 B9는 결과가 날짜 모양으로 보일 수 있어 서식을 확인해야 합니다.
오늘이 2026년 9월 24일이라고 할 때, B2에서 이름을 바꿔 가며 고른 결과입니다.
| B2에서 고른 이름 | D2 위치 | B4 연락처 | B5 생년월일 | B6 지역 | B7 최근 상담일 | B8 만 나이 | B9 지난 날 |
|---|---|---|---|---|---|---|---|
| 이서연 | 2 | 010-0000-7315 | 1985-09-24 | 경기 수원 | 2026-08-14 | 41 | 41 |
| 최수아 | 4 | 010-0000-1947 | 1978-12-01 | 서울 송파 | 2026-06-30 | 47 | 86 |
| 한지아 | 8 | 010-0000-4482 | 1994-02-14 | 인천 부평 | 2026-05-20 | 32 | 127 |
| (비움) | (빈칸) | (빈칸) | (빈칸) | (빈칸) | (빈칸) | (빈칸) | (빈칸) |
B2를 선택하고 Delete로 이름을 지우면 카드 전체가 빈칸이 되어 새로 고를 준비가 됩니다. 최수아 님처럼 마지막 상담 후 지난 날이 긴 고객을 바로 알아볼 수 있습니다.
수식 풀어 보기 — 안쪽부터 한 겹씩
B2에서 최수아 님을 골랐을 때를 따라가 보겠습니다.
MATCH(B2,'고객명단'!$A$2:$A$9,0)→ 이름 범위 A2:A9에서 최수아가 몇 번째인지 찾습니다. 결과는4. 마지막0은 정확히 같은 값만 찾으라는 뜻입니다.IFERROR(4,"")→ 오류가 아니므로 4를 그대로 D2에 둡니다. B2가 비어 있어 MATCH가#N/A를 낼 때만 빈칸("")으로 바꿉니다.$D$2=""→ D2가 4이므로FALSE. 그래서 IF는 세 번째 인수를 계산합니다.INDEX('고객명단'!$B$2:$B$9,4)→ 연락처 범위의 네 번째 칸010-0000-1947. B5·B6·B7도 가져오는 열만 다를 뿐 같은 4번째 칸을 꺼냅니다.DATEDIF(B5,TODAY(),"Y")→ 카드에 나온 생년월일 1978-12-01부터 오늘까지 꽉 찬 햇수47.TODAY()-B7은 날짜끼리 뺀 날 수86입니다.
위치를 D2에서 한 번만 찾는 것이 이 카드의 요령입니다. 항목이 늘어도 INDEX 수식의 열만 바꿔 복사하면 되고, 명단 위치가 눈에 보이니 결과가 이상할 때 어디서 틀렸는지 확인하기도 쉽습니다. D2가 거슬리면 글자색을 흐리게 하거나 열을 숨겨도 계산은 그대로입니다.
DATEDIF는 주의해서 쓰는 함수입니다. Microsoft 문서는 DATEDIF가 옛 Lotus 1-2-3 통합 문서를 지원하려고 제공되며 특정 상황에서 잘못된 결과를 낼 수 있다고 적고, 특히 "MD" 단위는 쓰지 말라고 안내합니다. 여기서 쓰는 "Y"는 만 나이를 구하는 단위입니다. 보험나이는 기준이 달라 이 값과 다를 수 있으니, 보험사·상품마다 기준을 확인해야 합니다.
자주 막히는 곳
| 증상 | 원인 | 해결 |
|---|---|---|
카드 전체가 #N/A | IFERROR 없이 MATCH만 쓴 상태에서 B2가 비어 있거나 명단에 없는 이름입니다. | D2를 IFERROR(MATCH(…),"")로 감싸고, B4 이하를 IF($D$2="","",…)로 막습니다. |
생년월일이 31314 같은 숫자 | 날짜 일련번호가 일반 서식으로 보입니다. | B5·B7을 간단한 날짜 서식으로 바꿉니다. |
| 새 고객이 드롭다운에 안 나옴 | 원본 범위를 A2:A9로 고정해 두었습니다. | 원본 범위와 INDEX·MATCH 범위를 함께 늘립니다. Microsoft 문서는 목록 항목을 표로 만들어 두면 항목을 추가하거나 지울 때 드롭다운이 자동으로 업데이트된다고 안내합니다. |
| 목록에 없는 이름을 쳐도 들어감 | 데이터 유효성 검사 창의 오류 경고 탭에서 스타일이 경고나 정보로 되어 있습니다. | 스타일을 중지로 두면 목록에 없는 값은 고쳐야만 넘어갈 수 있습니다. |
| 동명이인 중 첫 사람만 나옴 | MATCH는 정확히 같은 값 가운데 첫 번째 위치를 돌려줍니다. | 명단 이름 뒤에 생년월일이나 구분 글자를 붙여 겹치지 않게 만듭니다. |
IFERROR는 수식을 다 만든 뒤에 씌우세요. IFERROR는 #N/A뿐 아니라 #REF!, #VALUE!, #NAME? 같은 다른 오류도 모두 두 번째 인수로 바꿉니다. 범위를 잘못 잡거나 함수 이름을 틀려도 카드가 조용히 비어 보일 수 있으니, IFERROR 없이 이름을 몇 개 골라 결과가 맞는지 먼저 확인하고 마지막에 감싸는 편이 안전합니다.
다른 방법
1. 한 칸에 모두 넣기 (위치 칸 없이)
B4: =IFERROR(INDEX('고객명단'!$B$2:$B$9,MATCH($B$2,'고객명단'!$A$2:$A$9,0)),"")D2 없이 항목마다 IFERROR + INDEX + MATCH를 통째로 넣는 방식입니다. 결과는 완성 수식과 같습니다. 칸이 적어 깔끔하지만 항목 수만큼 같은 MATCH를 반복하게 됩니다. 시험 문제처럼 결과 칸 하나에 수식을 완성해야 할 때는 이 형태가 맞습니다.
2. 수식 하나를 아래로 채우기 (머리글로 열 찾기)
B4: =IFERROR(INDEX('고객명단'!$A$2:$E$9,MATCH($B$2,'고객명단'!$A$2:$A$9,0),MATCH(A4,'고객명단'!$A$1:$E$1,0)),"")카드 왼쪽 항목 이름(A4 연락처, A5 생년월일…)을 고객명단 머리글과 똑같이 적어 두면, 두 번째 MATCH가 머리글 행에서 그 항목이 몇 번째 열인지 찾습니다. B4에 한 번 쓰고 B7까지 채우면 네 항목이 모두 나옵니다. 카드에 항목을 추가할 때도 A열에 머리글과 같은 이름만 적고 수식을 한 칸 더 채우면 됩니다.
3. 문자 보낼 때 쓸 한 줄 만들기
=IF($D$2="","",B2&" / "&B4&" / "&TEXT(B5,"yyyy-mm-dd"))이서연 님을 고르면 이서연 / 010-0000-7315 / 1985-09-24가 나옵니다. 날짜를 &로 그냥 붙이면 일련번호가 붙기 때문에 TEXT로 날짜 모양의 글자로 바꿔 이어 붙였습니다.
4. XLOOKUP (Microsoft 365·엑셀 2021 이후)
새 버전에서는 =XLOOKUP($B$2,'고객명단'!$A$2:$A$9,'고객명단'!$B$2:$B$9,"")처럼 못 찾았을 때 보여 줄 값을 네 번째 인수에 넣어 IFERROR 없이 쓸 수 있습니다. Microsoft 문서에 따르면 XLOOKUP은 엑셀 2016과 2019에서는 쓸 수 없으므로, 여러 사람이 함께 쓰는 파일이라면 INDEX·MATCH가 무난합니다.
자주 묻는 질문
드롭다운 원본을 다른 시트 범위로 잡아도 되나요?
됩니다. Microsoft 문서의 예도 다른 워크시트의 A2:A9 범위를 원본으로 선택합니다. 원본 상자를 클릭한 상태에서 고객명단 시트 탭을 누르고 범위를 끌어 선택하면 시트 이름이 들어간 주소가 원본 상자에 들어갑니다.
드롭다운을 없애고 싶어요.
B2를 선택하고 데이터 유효성 검사 창을 다시 연 뒤 모두 지우기를 누르고 확인을 누르면 됩니다. 카드의 수식은 드롭다운과 별개라 그대로 남으므로, B2에 이름을 직접 입력해도 카드는 채워집니다.
카드에 항목을 더 넣고 싶어요.
고객명단에 열을 추가했다면 카드에 행을 하나 더 만들고 INDEX의 가져올 범위만 새 열로 바꿔 넣으면 됩니다. 위치는 D2에서 이미 찾아 두었으므로 MATCH는 다시 쓰지 않아도 됩니다.
함께 보면 좋은 글
- 엑셀 INDEX MATCH 함수 사용법 총정리 — 두 함수의 기본 문법
- 엑셀 INDEX MATCH 다중 조건과 2차원 찾기 — 행과 열을 함께 찾는 방법
- 엑셀 표 기능 사용법 범위를 표로 바꾸기 — 늘어나는 명단 관리
- 엑셀 함수 목차 — 목적별 함수 조합 시리즈 전체 보기
댓글
댓글 남기기