데이터 매핑은 엑셀 활용에서 빼놓을 수 없는 핵심 작업입니다. 몇 가지 유용한 매핑 기법만 익혀두면 작업 시간을 크게 단축하고 업무 흐름도 한층 개선할 수 있습니다. 이 글에서는 VLOOKUP 함수를 활용해 엑셀에서 데이터를 매핑하는 4가지 실용적인 방법을 소개합니다. 나아가 특정 값의 N번째 출현 값을 구하는 방법과 VLOOKUP 사용 시 발생하는 오류를 숨기는 요령까지 함께 다룹니다.
아래 링크에서 연습용 워크북을 내려받아 직접 따라 해볼 수 있습니다.
엑셀 VLOOKUP으로 데이터를 매핑하는 4가지 방법
이 글에서는 VLOOKUP 함수를 MATCH, COUNTIF, INDIRECT, IF 함수와 조합하여 데이터를 매핑하는 방법을 단계별로 살펴봅니다. 그럼 바로 시작해 보겠습니다!
여기서는 Microsoft Excel 365 버전을 기준으로 설명하지만, 사용 중인 버전에 맞춰 동일하게 적용할 수 있습니다.
방법 1: VLOOKUP 함수로 기본 데이터 매핑하기
가장 먼저 소개할 방법은 VLOOKUP 함수를 단독으로 사용하는 가장 기본적인 방식입니다.
예시로 B4:D14 셀에 직원 ID, 이름, 부서 정보가 담긴 데이터셋이 있다고 가정해 보겠습니다.
단계:
- 먼저 G5 셀로 이동한 뒤 아래 수식을 입력합니다.
=VLOOKUP(G4,B5:D14,3,FALSE)
이 수식에서 G4 셀은 조회할 ID(1008)를 가리키며, B5:D14 범위는 ID, 이름, 부서 열 전체를 의미합니다.
수식 분석:
- VLOOKUP(G4,B5:D14,3,FALSE) → 테이블의 가장 왼쪽 열에서 값을 찾은 후, 지정한 열의 같은 행에 있는 값을 반환합니다. 여기서 G4(lookup_value)가 B5:D14(table_array) 범위에서 매핑됩니다. 3(col_index_num)은 반환할 열 번호이며, 마지막 FALSE(range_lookup)은 정확히 일치 조건을 뜻합니다.
- 결과 → Marketing
최종 결과는 아래 이미지와 같습니다.
방법 2: VLOOKUP + MATCH 함수 조합으로 양방향 조회(Two-way Lookup)
두 번째 방법은 VLOOKUP과 MATCH 함수를 결합해 특정 행과 열이 교차하는 지점의 값을 찾는 방식입니다. 흔히 양방향 VLOOKUP(Two-way VLOOKUP)이라고 부릅니다.
B4:E12 셀에 판매 목록 데이터셋이 있고, 1월, 2월, 3월별 품목별 판매 수량이 정리되어 있다고 가정합니다.
단계:
- 먼저 조회할 품목과 월을 선택합니다. 예시에서는 각각 Television과 March를 선택했습니다.
- 다음으로 H6 셀에 아래 수식을 입력합니다.
=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)
여기서 H4와 H5 셀은 각각 품목과 월을 가리키며, B5:E5는 열 제목 행입니다.
수식 분석:
- MATCH(H5, B5:E5, 0) → 배열 안에서 주어진 값과 일치하는 항목의 상대적 위치를 반환합니다. H5는 조회 값(3월), B5:E5는 검색 대상 배열(lookup_array), 0은 정확히 일치를 의미하는 선택 인수(match_type)입니다.
- 결과 → 4
- VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) → 정리하면
- VLOOKUP(H4, B6:E10, 4, FALSE) → H4(lookup_value)를 B6:E10(table_array) 범위에서 찾고, 4(col_index_num)번째 열의 값을 반환합니다. FALSE(range_lookup)은 정확히 일치 조건입니다.
- 결과 → 243
최종 결과는 아래 그림과 같습니다.
방법 3: VLOOKUP + COUNTIF 함수로 데이터 매핑하기
세 번째 방법은 VLOOKUP 함수 안에 COUNTIF 함수를 넣어 활용하는 것입니다. 절차가 간단하니 아래 단계를 따라 해 보세요.
B4:C11 셀에 베스트셀러 도서와 해당 가격(USD)이 정리된 데이터셋이 있다고 가정합니다.
단계:
- 먼저 F5 셀로 이동해 아래 수식을 입력합니다.
=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)
이 수식에서 B5:C9 범위는 베스트셀러 도서와 가격 열을 나타내며, F4 셀은 조회할 도서명(예: House of Wisdom)을 가리킵니다.
수식 분석:
- COUNTIF(B5:B9,F4) → 지정한 범위에서 조건을 만족하는 셀의 개수를 셉니다. B5:B9는 검색 범위(range), F4는 조건(criteria)으로 일치 값의 개수를 반환합니다.
- 결과 → 1
- VLOOKUP(F4,B5:C9,2,TRUE) → F4(lookup_value)를 B5:C9(table_array) 범위에서 찾아 2(col_index_num)번째 열의 값을 반환합니다. TRUE(range_lookup)은 근사값 일치 조건입니다.
- 결과 → 25
- IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) → 정리하면
- IF(1,25,0) → 조건이 참인지 확인해 참이면 첫 번째 값, 거짓이면 두 번째 값을 반환합니다. 논리 검사 결과가 1(TRUE)이므로 25(value_if_true)를 반환하고, 그렇지 않으면 0(value_if_false)을 반환합니다.
- 결과 → $25
완성된 결과는 아래 스크린샷과 같습니다.
방법 4: VLOOKUP + INDIRECT 함수로 데이터 매핑하기
INDIRECT 함수와 VLOOKUP 함수를 결합해서도 데이터를 매핑할 수 있습니다. 단계별로 살펴보겠습니다.
B4:I10 셀에 미국 내 3개 도시별 동일 품목의 가격이 정리된 장바구니 목록 데이터셋이 있다고 가정합니다.
단계:
- 먼저 B5 셀을 선택한 뒤 데이터 탭 >> 데이터 유효성 검사 드롭다운을 클릭합니다.
데이터 유효성 대화 상자가 열립니다.
- 다음으로 제한 대상 드롭다운에서 목록을 선택하고, 원본 필드에 B6:B10 셀을 지정한 뒤 확인을 누릅니다.
- 이어서 B6:C10 셀을 선택하고 수식 탭 >> 이름 정의 옵션을 더블클릭합니다.
이름 편집 마법사가 열립니다.
- 여기서 데이터 범위에 적절한 이름(예: Boston)을 입력하고 확인을 클릭합니다.
같은 방식으로 Atlanta에 대한 이름 정의를 만듭니다.
마찬가지로 Denver에 대한 이름 정의도 생성합니다.
- 그다음 C14 셀로 이동해 데이터 탭에서 데이터 유효성 검사 버튼을 클릭합니다.
- 마찬가지로 목록 옵션을 선택하고, 아래 스크린샷처럼 앞서 만든 이름 정의들을 원본으로 입력합니다.
이제 드롭다운에서 품목과 지역을 선택합니다. 예시에서는 각각 Tomato와 Atlanta를 골랐습니다.
- 이후 D14 셀에 아래 수식을 입력합니다.
=VLOOKUP(B14,INDIRECT(C14),2,FALSE)
여기서 B14와 C14 셀은 각각 품목과 지역을 가리킵니다.
수식 분석:
- INDIRECT(C14) → 텍스트 문자열로 지정된 참조를 반환합니다. C14는 이름 정의된 범위(예: Boston)를 참조하는 ref_text 인수입니다.
- VLOOKUP(B14,INDIRECT(C14),2,FALSE) → B14(lookup_value)를 INDIRECT(C14)가 참조하는 이름 정의 범위(table_array)에서 찾아 2(col_index_num)번째 열의 값을 반환합니다. FALSE(range_lookup)은 정확히 일치 조건입니다.
- 결과 → $1.2
위 단계를 모두 마치면 결과는 아래 그림과 같습니다.
또한 이 방법은 데이터가 여러 워크시트에 나누어져 있고, 조회 값에 따라 특정 시트에서 데이터를 가져오고 싶을 때도 유용하게 활용할 수 있습니다.
VLOOKUP으로 N번째 출현 값 구하기
아래 B4:D13 셀에 문구류 판매 목록이 있다고 가정해 보겠습니다. 같은 고객이 구매한 2번째 또는 3번째 품목을 알고 싶다면 어떻게 해야 할까요? 이 문제는 COUNTIF와 VLOOKUP 함수를 활용하면 손쉽게 해결할 수 있습니다. 자세한 과정을 살펴보겠습니다.
단계:
- 먼저 B열에 새 열을 삽입하고 제목을 보조 열로 변경한 뒤, B5 셀에 아래 수식을 입력합니다.
=C5&COUNTIF($C$5:C5, C5)
📄 참고: 보조 열은 반드시 데이터셋의 가장 왼쪽에 삽입해야 합니다. VLOOKUP 함수는 기본적으로 왼쪽에서 오른쪽 방향으로만 검색하기 때문입니다.
여기서 C5 셀은 이름 John을 가리키며, COUNTIF 함수는 $C$5:C5 범위에서 John이 등장한 횟수를 셉니다. 마지막으로 &(앰퍼샌드) 연산자가 텍스트와 숫자를 하나로 결합합니다.
- 다음으로 조회할 이름과 순번을 입력합니다. 예시에서는 Julie와 3을 사용했습니다. 이후 H6 셀에 아래 수식을 입력합니다.
=VLOOKUP(H4&H5, B5:E13, 3, FALSE)
이 수식에서 H4와 H5 셀은 각각 이름과 순번을 나타냅니다.
모든 단계를 완료하면 결과는 아래 스크린샷과 같습니다.
VLOOKUP과 IF 함수로 #N/A 오류 숨기기
잘못된 조회 값을 입력했을 때 나타나는 #N/A 오류는 IF 함수 안에 ISNA와 VLOOKUP 함수를 조합하면 깔끔하게 숨길 수 있습니다. 아래 과정을 확인해 보세요.
단계:
- 먼저 G5 셀로 이동해 아래 수식을 입력합니다.
=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))
이 수식에서 G4 셀은 조회할 ID(1008)를 가리키며, B5:D14 범위는 ID, 이름, 부서 열을 나타냅니다.
수식 분석:
- ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → 값이 #N/A인지 확인해 TRUE 또는 FALSE를 반환합니다. G4(lookup_value)를 B5:D14(table_array) 범위에서 찾고, 3(col_index_num)번째 열의 값을 조회하며, FALSE(range_lookup)은 정확히 일치 조건입니다.
- 결과 → FALSE
- IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE)) → 정리하면
- IF(FALSE, "",VLOOKUP(G4, B5:D14,3,FALSE)) → 논리 검사 결과가 FALSE이므로 VLOOKUP 함수의 결과값을 반환하고, 오류가 발생하면 빈 값 ""을 반환합니다.
- 결과 → HR
최종 결과는 아래 그림과 같습니다.
추가로, 존재하지 않는 ID(예: ID 1012)를 입력하면 #N/A 오류 대신 빈 값이 반환되는 것을 아래 스크린샷에서 확인할 수 있습니다.
연습 섹션
각 시트 오른쪽에는 연습 섹션이 준비되어 있으니, 배운 내용을 직접 실습해 보시기 바랍니다. 스스로 따라 해 보는 것이 실력 향상에 가장 큰 도움이 됩니다.
마무리
이 글에서 소개한 VLOOKUP을 활용한 엑셀 데이터 매핑 방법들이 실무 스프레드시트 작업에 실질적인 도움이 되기를 바랍니다. 궁금한 점이나 피드백이 있다면 댓글로 알려주세요. 이 사이트의 다른 엑셀 함수 관련 글들도 함께 확인해 보시면 좋습니다.