VLOOKUP 왼쪽 조회: 6행 직원표로 INDEX·MATCH와 XLOOKUP 검산하기

사번은 D열에 있고 이름은 왼쪽 B열에 있는 직원표를 생각해 보겠습니다. D열에서 사번을 찾은 뒤 B열의 이름을 가져오는 것이 이번 과업입니다. 원본 열을 옮기는 대신 INDEX·MATCH, XLOOKUP, 보조열 VLOOKUP을 같은 여섯 행에서 비교합니다.

정상 입력은 숫자 10045, 기대 이름은 이도현입니다. 없는 숫자 99999와 문자형 10045도 따로 확인합니다. 이름·부서·입사일·사번은 모두 가상 연습값이며 실제 인사 정보가 아닙니다. 첨부 XLSX에는 본문과 같은 셀 배치, 수식, 독립 기대값이 있습니다. 실제 Excel 실행을 인증한 자료는 아닙니다.

VLOOKUP 왼쪽 조회를 설명하는 기존 이미지입니다. 실제 직원 자료나 Excel 실행 화면으로 제시하지 않습니다.

준비 환경과 방법 선택

세 방법을 함께 비교하려면 XLOOKUP을 지원하는 Microsoft 365, Excel 2021 또는 Excel 2024에서 사본을 엽니다. Excel 2016과 Excel 2019에서는 XLOOKUP을 사용할 수 없으므로 INDEX·MATCH와 보조열 VLOOKUP만 비교합니다. 첨부의 XLOOKUP 칸이 인식되지 않는 것을 직원 조회 실패와 혼동하지 않습니다. Microsoft XLOOKUP 설명

파일에는 직원표, 검산, 문자키, README 시트가 있습니다. 매크로와 외부 연결은 없습니다. 연노랑은 입력, 회색은 기대값이며 시트 잠금은 적용하지 않았습니다. 본문의 H2·I2·J2는 직원표 시트의 셀입니다.

VLOOKUP은 지정한 표 범위의 첫 열에서 값을 찾습니다. B:D를 지정하면 첫 열은 사번이 아닌 이름입니다. D:F를 지정하면 이름 원본 B열이 범위 밖입니다. 이 때문에 단순히 반환 열 번호를 음수로 바꿔 해결할 수는 없습니다. Microsoft VLOOKUP 설명

입력: 여섯 직원과 하나의 조회 사번

직원표 A1:F7은 다음과 같습니다. 표의 입사일은 가상 날짜 문자열이며 이번 조회 조건이나 날짜 계산에는 사용하지 않습니다. F열은 고정 이름 복사가 아니라 B열을 참조하는 수식입니다.

G2에는 숫자 10045를 입력합니다. 이 표는 D2:D7이 모두 숫자라는 전제입니다. 표시 모양만 보고 판단하지 않도록 L2에는 =ISNUMBER(G2), N2에는 =COUNT($D$2:$D$7)를 두었습니다. 정상 입력에서 각각 TRUE와 6이 기대값입니다.

1단계: MATCH 위치를 INDEX에 연결하기

직원표 H2의 수식입니다.

=IFNA(INDEX($B$2:$B$7,MATCH(G2,$D$2:$D$7,0)),"없음")

안쪽 MATCH가 사번의 상대 위치를 만들고 INDEX가 같은 위치의 이름을 반환합니다. 10045는 D2:D7의 다섯 번째이므로 위치는 5, 이름은 B2:B7의 다섯 번째인 이도현입니다. 워크시트 행 번호 6을 INDEX의 상대 위치 6으로 넣으면 다른 사람을 가져오므로 구분합니다.

중간 위치를 따로 보려면 K2의 수식을 읽습니다.

=IFNA(MATCH(G2,$D$2:$D$7,0),"없음")

IFNA는 이 예제에서 미발견의 #N/A를 없음으로 표시합니다. 반환 범위를 벗어난 #REF!까지 정상 결과로 바꾸는 장치는 아닙니다. INDEX와 MATCH를 연결해 조회 방향의 제약을 피하는 구조는 Microsoft의 INDEX·MATCH 조회 안내에서도 확인할 수 있습니다.

2단계: XLOOKUP으로 두 범위를 직접 지정하기

지원 환경의 I2에는 다음 수식을 사용합니다.

=XLOOKUP(G2,$D$2:$D$7,$B$2:$B$7,"없음",0)

D2:D7에서 조회하고 B2:B7에서 같은 위치의 값을 돌려받습니다. 반환 열이 왼쪽에 있어도 두 범위를 각각 지정할 수 있습니다. 없음은 미발견 표시이고, 마지막 0은 정확 일치 설정입니다. XLOOKUP 자체도 기본 일치 방식은 정확 일치입니다. Microsoft XLOOKUP 설명

조회 방향을 설명하는 이미지입니다. 실제 계산 기준은 직원표의 G2와 H2:J2입니다.

3단계: 보조열로 VLOOKUP 양식 유지하기

F2에 =B2를 입력해 F7까지 채운 상태에서 J2를 계산합니다.

=IFNA(VLOOKUP(G2,$D$2:$F$7,3,FALSE),"없음")

D:F 안에서 D열은 첫 번째, F열은 세 번째입니다. FALSE는 정확 일치입니다. 반환 열을 B열로 거꾸로 움직이는 것이 아니라, 사번 오른쪽에 이름을 참조하는 보조열을 만들어 조회하는 방식입니다. VLOOKUP의 마지막 인수를 생략하면 기본 근사 일치를 사용하므로 사번 조회에서는 의도를 명시합니다. Microsoft VLOOKUP 설명

이름을 고쳤을 때 F열도 함께 바뀌어야 합니다. F6에 다른 이름을 고정 입력하면 H2와 I2는 이도현인데 J2만 다른 이름일 수 있습니다. 이런 경우 함수를 전부 바꾸기 전에 F6이 B6을 참조하는지 확인합니다.

입력별 예상 결과와 형식 검사

다음은 원본 여섯 행을 바꾸지 않았을 때의 가상 기대값입니다. 문자형 숫자는 입력할 때 앞에 작은따옴표를 붙여 텍스트로 저장합니다. 작은따옴표 자체를 검색하는 사례는 아닙니다.

M2는 COUNTIF만으로 형식 일치까지 판단하지 않도록 입력과 원본의 숫자 전제를 먼저 확인합니다.

=IF(AND(ISNUMBER(G2),COUNT($D$2:$D$7)=6),COUNTIF($D$2:$D$7,G2),"형식 확인")

이 식은 이 파일의 여섯 숫자 사번에 한정한 점검입니다. 형식 확인이라는 문구를 미발견 0이나 중복 2와 같은 의미로 읽지 않습니다. 실무 사번이 문자 규칙이면 이 숫자 전제 자체를 적용할 수 없습니다.

검산 시트에는 표의 다섯 사례가 독립적으로 들어 있습니다. M열은 INDEX·VLOOKUP·위치·형식 검산, N열은 XLOOKUP 검산입니다. 지원 환경에서는 M2:N6이 모두 일치가 기대값입니다. XLOOKUP 미지원 환경에서는 N열을 통과시키려고 오류를 덮어쓰지 않고 비교 대상에서 분리합니다.

조회 결과 비교를 설명하는 이미지입니다. 미리 작성한 기대값을 실제 Excel 실행 증거로 사용하지 않습니다.

중복 사번: 정확 일치와 고유성은 다릅니다

G2를 숫자 10045로 복원한 뒤 사본의 D3을 10042에서 10045로 바꿔 봅니다. 이제 박지우와 이도현의 사번이 같습니다. 이 가상 변경에서는 M2가 2, K2가 2, 세 조회 결과가 박지우가 되는 것이 예상입니다. 오류 표시가 없어도 한 사람을 고유하게 골랐다는 뜻은 아닙니다.

복구는 사본의 D3을 원래 10042로 되돌리는 것입니다. M2=1, K2=5, 이름 이도현을 다시 비교합니다. 실제 직원 데이터에서 중복이 발견됐다고 한 행을 임의 삭제해서는 안 됩니다. 원본 담당자에게 중복이 잘못된 입력인지, 사번 외의 키가 필요한 구조인지 먼저 확인합니다.

정확 일치, 형식 검사, 고유성 확인은 서로 다른 점검입니다.

실패와 복구: 범위의 길이뿐 아니라 시작 행도 확인하기

시작 행을 한 칸 밀어도 범위 길이는 여전히 여섯 칸일 수 있습니다. 그래서 행 수가 같다는 검사만으로 안전하지 않습니다. 검색 범위의 첫 항목과 반환 범위의 첫 항목이 같은 레코드여야 합니다. 반대로 끝 행만 길어진 MATCH 범위가 기존 일치 위치를 모두 바꾸는 것은 아닙니다. 새로 포함된 위치가 짧은 반환 범위를 벗어날 때 오류가 생길 수 있습니다.

8행에 새 직원과 사번 10047을 추가하는 확장 실습도 사본에서 합니다. 기존 2~7행 수식은 그 새 행을 보지 못합니다. 검색 범위, 이름 반환 범위, 보조열을 모두 같은 새 끝 행까지 확장하고, M2의 숫자 개수 전제 6도 새 원본 행 수에 맞춰 재설계해야 합니다. 일부 범위만 늘린 뒤 없음이 사라졌다는 이유로 끝내지 않습니다.

문자 사번의 앞자리 0은 임의로 복원하지 않기

문자키 시트에는 문자 00123과 문자 123을 서로 다른 키로 두었습니다. 같은 문자키로 조회하면 각각 앞자리0 포함과 세자리 코드를 반환하고, 숫자 123으로 조회하면 없음이 기대값입니다. 이 비교는 원본 키 규칙이 중요하다는 것을 보여 주는 별도 가상 예제입니다.

이미 숫자로 저장되어 앞자리 0이 사라졌다면 표시 서식이나 TEXT의 @만으로 원래 몇 자리였는지 알아낼 수 없습니다. 길이가 정해진 코드인지 원본 규칙을 확인해야 합니다. 숫자 사번 예제의 복구와 문자 사번 데이터의 정규화를 같은 작업으로 취급하지 않습니다.

잘못된 값과 범위를 구분해 복구하는 설명용 이미지입니다.

실습 순서와 적용 한계

첨부 파일 상태 확인(2026-10-02): 현재 배포된 XLSX의 검산 시트 등 일부 셀에 저장된 계산값이 #NAME?로 남아 있습니다. 오류 표시가 없는 저장값도 대조해야 합니다. 문자키!F3(문자 ‘123’)와 F4(숫자 123)의 저장값은 모두 ‘앞자리0 포함’이지만, 같은 시트의 고정 기대값 G3·G4는 각각 ‘세자리 코드’·‘없음’입니다. 재계산한 뒤 이 두 셀도 기대값과 비교하세요. 파일 내부의 수식과 저장값을 확인한 결과이며, Microsoft Excel에서 다시 계산한 결과는 아직 검증하지 못했습니다. 미리보기의 오류나 저장된 확인 문구만으로 조회 수식의 성공·실패를 판단하지 마세요. 본문의 여섯 행을 새 사본에 직접 입력해 이름·위치·형식을 대조할 수 있습니다. 첨부를 사용할 경우 앱에서 다시 계산한 뒤 기대값과 비교하고, 오류가 계속되면 함수 지원 여부와 시트 참조부터 확인하세요. 특히 XLOOKUP 미지원과 다른 수식의 오류를 구분해야 합니다. 이 파일을 실제 직원 조회에 바로 사용하지 말고 검증용 사본에 한정하세요.

1. README에서 자신의 Excel 환경과 비교 가능한 수식을 확인합니다. 원본 파일은 보관하고 사본을 사용합니다.

2. 직원표 G2=숫자 10045에서 이름 이도현, 위치 5, 건수 1을 대조합니다.

3. 검산 시트의 정상·미발견·문자형·첫 행·마지막 행을 고정 기대값과 비교합니다.

4. 중복, 보조열 덮어쓰기, 반환 시작 행 이동을 한 번에 하나씩 시도합니다. 각 변경을 복원한 뒤 다음 사례로 넘어갑니다.

5. 문자키 시트에서 앞자리 0과 값의 형식을 구별하고, 마지막에는 직원표 G2를 숫자 10045로 복원해 저장합니다.

이 글은 하나의 고유한 사번으로 한 행을 찾는 범위입니다. 대소문자를 구분하는 키, 특수문자를 포함한 검색 규칙, 여러 중복 행을 전부 반환하는 작업까지 같은 결과를 보장하지 않습니다. 실제 명단의 규칙과 개인정보 처리는 별도로 확인하며 실습 파일에 회사 인사 자료를 넣어 공개하지 않습니다.

함께 읽을 글

• INDEX MATCH 위치부터 단가까지 단계별로 검산하기: MATCH 위치와 INDEX 단독 결과를 다른 셀에서 확인합니다.

• XLOOKUP 전환 전 VLOOKUP 결과와 비교하기: 중복 행 검색 방향과 반환 범위를 더 살펴볼 때 이어 읽습니다.

공식 문서 확인일은 2026-09-12입니다. 함수 동작의 출처와 이 글의 가상 직원 입력은 구분했습니다. 계산 결과의 저장 미리보기나 설명용 그림을 실제 Office 실행 증빙으로 제시하지 않습니다.

작성자 소개

최지원

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

WP 게시물 작성자의 글 보기