VLOOKUP만 쓰다 한 번쯤 데인 분들께 — INDEX·MATCH와 XLOOKUP 실무 정리

모니터라는 이름을 찾았는데 결과가 P002가 아니라 350000이라면, 검색 성공 여부보다 반환 범위를 먼저 봐야 합니다. 같은 원본에서 코드와 단가는 다른 열에 있습니다. 이번 실습은 이름으로 왼쪽 코드를 찾는 조회, 코드로 오른쪽 이름을 찾는 조회, 행과 열을 함께 고르는 조회를 구분합니다.

제품 단가는 가상 연습 숫자이며 실제 판매 정보가 아닙니다. 기존 제품 네 행과 사번 네 행을 유지했고, 교차 조회를 위해 가상 월별 단가표를 더했습니다. 첨부의 수식 저장값을 검산했지만 실제 Microsoft Excel 앱을 실행한 경험이나 속도 측정으로 제시하지 않습니다.

모니터의 위치 2와 제품코드 P002를 나눠 읽기

제품조회!A1:C5의 원본은 다음과 같습니다. C열은 쉼표가 든 문자가 아니라 숫자이고 표시 서식으로 천 단위를 구분합니다.

E2에 모니터를 입력합니다. F2의 COUNTIF는 해당 이름의 개수를 세어 1을 반환합니다. 중복이 없는 이번 입력에서 G2의 MATCH 결과는 2이고 H2의 INDEX 결과는 P002입니다.

=MATCH(E2,B2:B5,0)
=INDEX(A2:A5,G2)
=XLOOKUP(E2,B2:B5,A2:A5,"없음",0)

위 세 줄은 위치 계산, 위치로 반환, 직접 조회의 핵심 부분입니다. 첨부 G2:I2는 이 앞에 개수 검사를 넣었습니다. 개수가 0이면 없음, 2 이상이면 중복 확인으로 남기므로 첫 제품을 임의로 승인하지 않습니다. 정상 입력에서 I2도 P002이고 J2는 TRUE입니다.

Microsoft MATCH 설명에 따르면 MATCH는 범위 안의 상대 위치를 반환합니다. 2는 워크시트 2행이라는 뜻이 아닙니다. B2:B5의 두 번째 셀 B3에서 모니터를 찾았으므로 A2:A5의 두 번째 셀 A3을 반환합니다.

오른쪽 조회를 왼쪽 조회와 같은 수식으로 보지 않기

E6은 코드 P002입니다. F6의 VLOOKUP은 A2:C5의 첫 열에서 코드를 찾고 두 번째 열의 모니터를 반환합니다. G6의 XLOOKUP도 같은 이름을 돌려주며 H6에서 둘을 대조합니다.

=VLOOKUP(E6,A2:C5,2,FALSE)
=XLOOKUP(E6,A2:A5,B2:B5,"없음",0)

VLOOKUP을 쓴 위 원본 범위는 코드가 첫 열입니다. 그 범위 그대로 제품명을 찾아 왼쪽 코드를 반환하는 것과는 다른 질문입니다. 배열을 다시 구성하는 우회 수식까지 불가능하다는 뜻은 아닙니다. 이번 파일은 원본을 이동하지 않고 검색 열과 반환 열을 명시하는 방법을 비교합니다.

열을 추가한 뒤에도 VLOOKUP의 숫자 2가 원하는 열인지 확인하세요. $는 복사할 때 참조 이동을 제어하는 표기이며, 잘못된 열 선택이나 원본 행의 대응까지 자동으로 고쳐 주지는 않습니다. 가격 숫자가 반환되면 코드·이름·단가 중 어느 범위를 반환하도록 썼는지 먼저 복구합니다.

행과 열을 모두 찾는 교차 조회

교차조회 시트는 코드 네 행과 1월·2월·3월 세 열을 둡니다. P002의 가상 단가는 350000·360000·355000입니다. F2=P002, G2=2월이면 H2의 행 위치 2, I2의 열 위치 2, J2의 결과 360000을 순서대로 확인합니다.

=MATCH(F2,A2:A5,0)
=MATCH(G2,B1:D1,0)
=INDEX(B2:D5,H2,I2)

Microsoft INDEX 문서는 행·열 번호로 선택한 위치의 값을 반환하는 배열 형식을 설명합니다. 이 예제에는 두 MATCH가 필요합니다. 단가 한 열 C2:C5에서 행만 찾는 수식을 양방향 조회라고 부르지 않습니다.

G2를 4월로 바꾸면 I2와 J2는 열 없음입니다. 본문에 없는 네 번째 월을 가장 가까운 열로 바꾸지 않습니다. 2월로 복구한 뒤 360000을 확인합니다. 실제 업무에서는 헤더 중복이나 코드 중복을 허용할지도 따로 정해야 합니다. 이 교차표의 기본 헤더와 코드는 각각 고유합니다.

사번 예제로 입력칸과 결과칸 분리하기

사번조회!B2:B5에는 김영업·이인사·박재무·최개발, D2:D5에는 E1001·E1002·E1003·E1004가 있습니다. F2의 E1002를 조회하면 I2는 2, G2와 H2는 이인사입니다. F2에는 수식을 쓰지 않습니다.

=INDEX($B$2:$B$5,MATCH(F2,$D$2:$D$5,0))
=XLOOKUP(F2,$D$2:$D$5,$B$2:$B$5,"없음",0)

이 두 줄은 기본 입력의 핵심 수식입니다. 첨부 G2는 이미 계산한 I2의 위치를 읽고, 미발견은 없음으로 구분합니다. E9999를 넣었을 때 두 결과가 없음인지 확인한 뒤 E1002로 복구하세요. 조회 일치는 신원 인증이나 개인정보 확인을 의미하지 않습니다.

오류 입력을 원상복구하는 순서

제품조회 E2에 앞 공백이 있는 모니터를 입력하면 없음입니다. 공백 제거가 맞는지는 원본 코드 규칙을 먼저 보고 결정합니다. 이 실습에서는 원본을 일괄 변환하지 않고 E2를 정확한 모니터로 되돌립니다.

B5의 마우스를 모니터로 바꾸면 F2는 2, H2와 I2는 중복 확인입니다. 반환된 코드가 한 개라고 원본도 고유하다고 판단하지 마세요. B5를 마우스로 복구해 개수 1과 P002를 확인합니다.

실제 범위를 삭제해 참조 오류가 나면 없음이라는 문구로 덮기 전에 A2:A5와 B2:B5의 행 대응을 복원합니다. IFNA는 미발견 오류를 처리하는 용도로 사용하며 모든 오류를 0으로 숨기는 IFERROR 대체식은 사용하지 않았습니다. 다만 첨부 사번조회!G2는 I2가 숫자가 아니면 없음으로 표시하므로, G2의 없음만으로 미발견과 참조 오류를 구별할 수 없습니다. I2와 조회 범위를 함께 확인하세요.

옵션과 지원 환경의 한계

Microsoft XLOOKUP 문서의 일치 모드 0은 정확 일치, -1은 다음 작은 값, 1은 다음 큰 값, 2는 와일드카드 비교입니다. 일치 모드 1을 부분 문자열 검색이라고 해석하면 안 됩니다. 검색 모드 -2도 대소문자 구분이 아니라 내림차순 정렬을 전제로 하는 이진 검색입니다. 이번 기본 수식은 정확 일치 0만 사용합니다. 이 실습은 *, ?, ~가 들어 있거나 <, >, =로 시작하는 조회값은 다루지 않습니다.

파일 사본을 받는 사람의 Excel에서 열고 재계산해 함수 인식 오류가 없는지 확인합니다. 함수 인식 오류가 나면 결과를 상수로 바꾸지 말고 해당 작업의 INDEX·MATCH 대안을 검산합니다. 작은 표가 맞는다고 대용량 처리 속도가 빨라졌다는 결론을 내리지 않습니다.

1. 제품조회 E2=모니터에서 개수 1, 위치 2, 코드 P002를 각각 확인합니다.

2. 코드 입력 E6=P002의 VLOOKUP·XLOOKUP 결과가 모두 모니터인지 비교합니다.

3. 교차조회 F2=P002와 G2=2월에서 두 위치 2와 결과 360000을 읽습니다.

4. 앞 공백·중복 이름·없는 열을 사본에서 시험하고 원래 입력으로 복구합니다.

XLOOKUP의 검색 모드 -1은 마지막 항목부터 검색합니다.

MATCH의 일치 종류 0은 처음 정확히 일치한 값의 위치를 찾습니다.

INDEX는 표나 범위에서 값 또는 값의 참조를 반환합니다.

VLOOKUP의 조회값은 지정한 표 범위의 첫 열에서 찾습니다.

출처 원문 수집일: S1 2026-09-24; S2 2026-09-24; S3 2026-09-24; S4 2026-09-24.

작성자 소개

최지원

엑셀·VBA·파이썬·AI 업무 자동화의 사용 절차와 실습 자료를 정리합니다.

WP 게시물 작성자의 글 보기