엑셀 VLOOKUP·XLOOKUP 완전정복｜원하는 데이터 자동으로 찾기

엑셀을 사용하다 보면 상품코드를 입력해 상품명이나 가격을 찾거나, 사번으로 직원의 부서와 직급을 확인해야 할 때가 있습니다.

데이터가 몇 개라면 직접 찾을 수 있지만 수백 개 이상이라면 시간이 많이 걸립니다.

이럴 때 사용하는 대표적인 검색 함수가 VLOOKUP과 XLOOKUP입니다.

1. VLOOKUP 함수란?

VLOOKUP은 표에서 특정 값을 찾고 같은 행에 있는 다른 정보를 가져오는 함수입니다.

기본 형식은 다음과 같습니다.

=VLOOKUP(찾을값,범위,열번호,FALSE)

예를 들어 다음과 같은 상품표가 있다고 하겠습니다.

상품코드	상품명	가격
A001	노트북	1,200,000
A002	모니터	350,000
A003	키보드	80,000

E2 셀에 A003을 입력하고 상품명을 찾으려면 다음과 같이 사용합니다.

=VLOOKUP(E2,A2:C4,2,FALSE)

결과는 키보드입니다.

가격을 찾고 싶다면 열번호를 3으로 바꿉니다.

=VLOOKUP(E2,A2:C4,3,FALSE)

결과는 80,000원입니다.

2. VLOOKUP의 열번호

VLOOKUP에서 초보자가 가장 헷갈리는 부분이 열번호입니다.

범위가 A2:D100이라면

A열 → 1
B열 → 2
C열 → 3
D열 → 4

가 됩니다.

따라서 D열의 값을 가져오려면 다음처럼 작성합니다.

=VLOOKUP(F2,A2:D100,4,FALSE)

여기서 열번호는 실제 엑셀 열 번호가 아니라 선택한 범위 안에서 몇 번째 열인지를 의미합니다.

3. FALSE는 무엇인가요?

VLOOKUP의 마지막에 들어가는 FALSE는 정확히 같은 값을 찾는다는 의미입니다.

=VLOOKUP(E2,A2:C100,2,FALSE)

상품코드, 사번, 주문번호처럼 정확히 같은 데이터를 찾을 때 대부분 FALSE를 사용합니다.

초보자는 우선 VLOOKUP 마지막에는 FALSE라고 기억해 두면 편합니다.

4. #N/A 오류 해결하기

찾는 값이 표에 없으면 #N/A 오류가 나타날 수 있습니다.

이때 IFERROR를 함께 사용하면 됩니다.

=IFERROR(VLOOKUP(E2,A2:C100,2,FALSE),"상품없음")

상품코드가 있으면 상품명이 표시되고, 없으면 '상품없음'이라고 표시됩니다.

5. XLOOKUP 함수란?

XLOOKUP은 VLOOKUP보다 사용 방법이 직관적인 검색 함수입니다.

기본 형식은 다음과 같습니다.

=XLOOKUP(찾을값,찾을범위,반환범위)

상품코드로 상품명을 찾으려면 다음과 같이 작성합니다.

=XLOOKUP(E2,A2:A100,B2:B100)

가격을 찾으려면 반환 범위만 바꾸면 됩니다.

=XLOOKUP(E2,A2:A100,C2:C100)

VLOOKUP처럼 열번호를 계산할 필요가 없다는 것이 장점입니다.

6. XLOOKUP은 왼쪽도 찾을 수 있습니다

VLOOKUP은 찾을 값이 선택한 범위의 가장 왼쪽에 있어야 한다는 제한이 있습니다.

XLOOKUP은 이런 제한이 없습니다.

예를 들어 B열의 상품명으로 A열의 상품코드를 찾을 수 있습니다.

=XLOOKUP(E2,B2:B100,A2:A100)

따라서 E2에 '키보드'를 입력하면 해당 상품코드를 가져올 수 있습니다.

7. 값이 없을 때 처리하기

XLOOKUP에서는 찾는 값이 없을 때 보여줄 문구도 바로 지정할 수 있습니다.

=XLOOKUP(E2,A2:A100,B2:B100,"상품없음")

별도로 IFERROR를 넣지 않아도 되어 수식이 간단합니다.

8. VLOOKUP과 XLOOKUP 차이

구분	VLOOKUP	XLOOKUP
열번호	필요	필요 없음
왼쪽 검색	제한적	가능
값이 없을 때	IFERROR 사용	문구 직접 지정
구버전 엑셀	사용 가능	일부 버전 미지원

XLOOKUP을 사용할 수 있다면 일반적으로 XLOOKUP이 편리합니다.

다만 오래된 회사 문서에는 VLOOKUP이 많이 사용되므로 두 함수 모두 기본 사용법을 알아두는 것이 좋습니다.

9. 검색이 안 될 때 확인할 것

분명히 같은 값인데 검색이 되지 않는다면 다음을 확인해 보세요.

첫째, 상품코드 앞뒤에 공백이 없는지 확인합니다.

둘째, 숫자가 문자 형식으로 저장되어 있지 않은지 확인합니다.

셋째, 검색 범위가 정확한지 확인합니다.

넷째, 상품코드나 사번에 오타가 없는지 확인합니다.

수식을 여러 행에 복사할 때는 범위를 $ 기호로 고정하면 편리합니다.

=VLOOKUP(E2,$A$2:$C$100,2,FALSE)

10. 마무리

VLOOKUP과 XLOOKUP은 엑셀에서 원하는 데이터를 빠르게 찾아주는 대표적인 함수입니다.

핵심 수식 두 가지만 기억해 두세요.

=VLOOKUP(찾을값,범위,열번호,FALSE)

=XLOOKUP(찾을값,찾을범위,반환범위)

VLOOKUP은 기존 문서에서 많이 사용되고, XLOOKUP은 열번호 없이 왼쪽과 오른쪽 데이터를 자유롭게 검색할 수 있어 편리합니다.

상품관리, 직원관리, 고객관리, 재고관리처럼 많은 데이터를 다룬다면 꼭 익혀두면 좋은 함수입니다.