엑셀 조회 수식: XLOOKUP, VLOOKUP, INDEX/MATCH 사용법

조회 수식은 참조 표에서 값을 찾아 그 옆에 있는 정보, 예를 들어 제품의 가격이나 직원의 부서를 가져와요. 주문마다 가격을 일일이 입력하는 대신 제품 목록을 하나만 두고 주문이 그 목록을 읽게 하는 거죠. 목록에서 가격을 한 번만 고치면 그 가격을 쓰는 모든 주문이 함께 바뀌어요.

이 글에서는 작은 주문 시트를 사용해요. 왼쪽에 주문이 있고, 주문이 읽어 오는 제품 목록은 F1:H5에 있어요.

	A	B	C	F	G	H
1	제품	가격	분류	제품	분류	가격
2	위젯 B			위젯 A	하드웨어	12
3	멀티탭		전자 제품	위젯 B	하드웨어	18
4	USB 허브		#N/A	케이블 타이	액세서리	3
5	케이블 타이	3	액세서리	멀티탭	전자 제품	22

A1:C5에 주문이, F1:H5에 제품 목록이 있어요. 주문의 가격과 분류 중 일부는 아직 비어 있고, USB 허브 주문에는 목록에 없는 제품이라서 #N/A가 표시돼요.

조회는 어떻게 동작할까요?

모든 조회는 똑같은 두 가지 일을 해요.

찾을 열 하나에서 정확히 일치하는 값을 찾아요. 여기서는 제품 열 F2:F5예요.
다른 열의 같은 행에 있는 값을 돌려줘요. 예를 들어 가격 열 H2:H5예요.

위젯 B의 가격을 찾을 때 엑셀은 F2:F5를 위에서부터 훑다가 목록의 두 번째 행(F3)에서 위젯 B를 찾고, H2:H5의 두 번째 행에 있는 값 18을 돌려줘요.

"정확히 일치"는 값 전체가 같아야 한다는 뜻이에요. 다만 영문 대소문자는 구분하지 않아요. 위젯 b는 위젯 B와 일치하지만, 끝에 공백이 하나 붙은 위젯 B 는 위젯 B와 일치하지 않아요.

참조 표는 다음 조건을 갖출 때 가장 잘 동작해요.

찾을 열의 값은 한 번씩만 나와요. 위젯 B가 두 번 있으면 조회는 처음 찾은 값을 돌려주고 나머지는 무시해요.
참조 표를 읽어 가는 데이터와 떨어져 있어요. 빈 열이나 빈 행을 사이에 두거나, 따로 시트를 만들어 두세요.
각 값은 참조 표에만 있어요. "오늘 가격이 이러니까" 하고 주문에 가격을 직접 입력하지 마세요. 주문을 최신 상태로 유지해 주는 게 바로 조회예요.

XLOOKUP으로 값 찾기

XLOOKUP은 최신 조회 함수예요. 무엇을 찾을지, 어디서 찾을지, 무엇을 돌려줄지 알려 주면 돼요.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

lookup_value는 찾을 값이에요. 보통 A2 같은 셀을 써요.
lookup_array는 값을 찾을 열이에요. 여기서는 F2:F5예요.
return_array는 값을 돌려줄 열이에요. 여기서는 H2:H5예요. lookup_array와 행 수가 같아야 해요.
if_not_found는 넣지 않아도 돼요. 일치하는 값이 없을 때 표시할 내용이에요.

XLOOKUP은 따로 지정하지 않으면 정확히 일치하는 값을 찾기 때문에, 대부분의 조회는 인수 세 개면 충분해요.

결과를 표시할 셀을 클릭하세요
위젯 B 주문의 가격 칸인 B2를 클릭하세요.
수식을 입력하세요
아래 수식을 입력하세요. $ 기호가 제품 목록을 고정해 주기 때문에, 수식을 다른 주문으로 복사해도 계속 F2:F5와 H2:H5를 가리켜요.
=XLOOKUP(A2,$F$2:$F$5,$H$2:$H$5)

Enter 키를 누르세요

B2에 위젯 B의 가격 18이 표시돼요. H3을 20으로 바꾸면 B2도 함께 바뀌어요.

	A	B	C	F	G	H
1	제품	가격	분류	제품	분류	가격
2	위젯 B	18		위젯 A	하드웨어	12
3	멀티탭		전자 제품	위젯 B	하드웨어	18
4	USB 허브		#N/A	케이블 타이	액세서리	3
5	케이블 타이	3	액세서리	멀티탭	전자 제품	22

XLOOKUP이 F2:F5에서 위젯 B를 찾아 두 번째 행에 있다는 걸 알아내고, H2:H5의 두 번째 값인 18을 돌려줘요.

가격 대신 분류를 가져오려면 돌려줄 범위만 바꾸세요. =XLOOKUP(A3,$F$2:$F$5,$G$2:$G$5)는 멀티탭의 분류인 전자 제품을 돌려줘요. 두 범위를 따로 지정하기 때문에 XLOOKUP은 찾을 열보다 왼쪽에 있는 열의 값도 돌려줄 수 있어요.

값이 없을 때 메시지 표시하기

USB 허브는 제품 목록에 없기 때문에 그냥 조회하면 "일치하는 값을 찾지 못했다"는 뜻의 #N/A가 나와요. 네 번째 인수를 넣으면 더 알아보기 쉬운 내용을 표시할 수 있어요. B4를 클릭하고 다음 수식을 입력하세요.

=XLOOKUP(A4,$F$2:$F$5,$H$2:$H$5,"없음")

B4에 없음이 표시돼요. 어떤 텍스트든 쓸 수 있고, 0 같은 숫자를 쓰거나 ""를 써서 셀을 비어 보이게 할 수도 있어요.

이런 메시지는 문제를 감추는 것보다 나아요. 주문에 목록에 아직 없는 제품이 들어 있다는 걸 알려 주니까, 제품을 추가하거나 오타를 고칠 수 있죠.

VLOOKUP 읽고 쓰기

VLOOKUP은 예전부터 쓰던 조회 함수예요. XLOOKUP이 나오기 전에 만든 통합 문서에는 거의 다 들어 있어서, 읽을 줄은 알아야 해요.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value는 찾을 값이에요.
table_array는 참조 표 전체예요. 여기서는 F2:H5예요. VLOOKUP은 항상 이 범위의 첫 번째 열에서 찾아요.
col_index_num은 돌려줄 열의 번호예요. table_array의 왼쪽부터 세요. 1은 제품, 2는 분류, 3은 가격이에요.
range_lookup에는 정확히 일치하는 값을 찾도록 FALSE를 입력하세요. 꼭 넣으세요. 생략하면 VLOOKUP은 유사 일치로 찾는데, 정렬되지 않은 목록에서는 오류 없이 엉뚱한 행의 값을 돌려줄 수 있어요.

위젯 B의 분류를 가져오려면 C2를 클릭하고 다음 수식을 입력하세요.

=VLOOKUP(A2,$F$2:$H$5,2,FALSE)

C2에 하드웨어가 표시돼요. 2를 3으로 바꾸면 같은 수식이 가격 18을 돌려줘요.

	A	B	C	F	G	H
1	제품	가격	분류	제품	분류	가격
2	위젯 B	18	하드웨어	위젯 A	하드웨어	12
3	멀티탭		전자 제품	위젯 B	하드웨어	18
4	USB 허브		#N/A	케이블 타이	액세서리	3
5	케이블 타이	3	액세서리	멀티탭	전자 제품	22

VLOOKUP이 F2:H5의 첫 번째 열에서 위젯 B를 찾고, 그 행의 두 번째 열에 있는 값인 하드웨어를 돌려줘요.

VLOOKUP에는 XLOOKUP에 없는 한계가 두 가지 있어요.

오른쪽으로만 찾아요. 찾을 열이 table_array의 첫 번째 열이어야 해요. 가격이 제품 이름보다 왼쪽에 있으면 VLOOKUP으로는 가져올 수 없어요.
열 번호를 숫자로 입력해요. 표 안에, 예를 들어 분류와 가격 사이에 열을 삽입하면 가격은 4번째 열로 밀려나지만 수식은 여전히 3번째 열을 가져와요. 오류도 없이 엉뚱한 열의 값을 돌려주게 되죠.

VLOOKUP의 #N/A를 바꾸려면 IFNA로 감싸세요. =IFNA(VLOOKUP(A4,$F$2:$H$5,3,FALSE),"없음")처럼요.

INDEX와 MATCH 함께 쓰기

XLOOKUP이 나오기 전에는 숙련된 사용자들이 두 함수를 짝지어서 같은 유연성을 얻었어요. 지금도 모든 버전의 엑셀에서 쓸 수 있어요.

MATCH는 목록에서 값이 몇 번째에 있는지 찾아요.

=MATCH(A3,$F$2:$F$5,0)

멀티탭은 F2:F5의 4번째 항목이라서 이 수식은 4를 돌려줘요. 마지막 인수 0은 정확히 일치를 뜻해요. 꼭 입력하세요. 넣지 않으면 MATCH는 목록이 정렬되어 있다고 보고 찾는 값보다 작은 값 가운데 가장 가까운 값을 찾아요.

INDEX는 목록에서 지정한 위치의 값을 돌려줘요. =INDEX($H$2:$H$5,4)는 4번째 가격인 22를 돌려줘요.

MATCH를 INDEX 안에 넣으면 조회가 돼요. B3을 클릭하고 다음 수식을 입력하세요.

=INDEX($H$2:$H$5,MATCH(A3,$F$2:$F$5,0))

	A	B	C	F	G	H
1	제품	가격	분류	제품	분류	가격
2	위젯 B	18	하드웨어	위젯 A	하드웨어	12
3	멀티탭	22	전자 제품	위젯 B	하드웨어	18
4	USB 허브		#N/A	케이블 타이	액세서리	3
5	케이블 타이	3	액세서리	멀티탭	전자 제품	22

MATCH가 F2:F5에서 멀티탭이 4번째에 있다는 걸 찾고, INDEX가 H2:H5의 4번째 값인 22를 돌려줘요.

B3에 22가 표시돼요. INDEX/MATCH도 XLOOKUP처럼 두 범위를 따로 쓰기 때문에 찾을 열보다 왼쪽에 있는 열의 값을 돌려줄 수 있고, 열을 삽입해도 깨지지 않아요.

어떤 함수를 써야 할까요?

	XLOOKUP	VLOOKUP	INDEX/MATCH
기본이 정확히 일치	예	아니요: `FALSE` 추가	아니요: `0` 추가
왼쪽 열의 값 가져오기	예	아니요	예
열을 삽입해도 안전	예	아니요	예
"없음" 메시지 기능 내장	예	`IFNA`로 감싸기	`IFNA`로 감싸기
Excel 2019 이하에서 동작	아니요	예	예

파일을 여는 사람이 모두 Microsoft 365나 Excel 2021 이후 버전을 쓴다면 XLOOKUP을 쓰세요. 이전 버전에서도 동작해야 하는 파일이라면 INDEX/MATCH를 쓰세요. VLOOKUP은 기존 통합 문서를 읽고 고칠 수 있을 만큼 알아 두세요.

	A	B	C	F	G	H
1	제품	가격	분류	제품	분류	가격
2	위젯 B	18	하드웨어	위젯 A	하드웨어	12
3	멀티탭	22	전자 제품	위젯 B	하드웨어	18
4	USB 허브	없음	#N/A	케이블 타이	액세서리	3
5	케이블 타이	3	액세서리	멀티탭	전자 제품	22

한 시트에 세 가지 방법을 모두 썼어요. B2와 B4는 XLOOKUP, C2는 VLOOKUP, B3은 INDEX/MATCH예요.

조회 결과가 #N/A인 이유는?

값이 정말 목록에 없어요. 철자를 확인하고, 빠진 항목을 추가하거나 값이 없을 때 표시할 인수를 쓰세요.
공백이 더 있어요. 끝에 공백이 붙은 위젯 B 는 위젯 B와 일치하지 않아요. TRIM으로 값을 정리하거나 다시 입력하세요.
숫자가 텍스트로 저장되어 있어요. 숫자 1001로 저장된 제품 코드는 텍스트 "1001"과 일치하지 않아요. 예를 들어 숫자로 변환으로 두 열의 형식을 똑같이 맞추세요.
수식을 복사할 때 범위가 밀렸어요. $ 기호가 없으면 다음 행에서 F2:F5가 F3:F6으로 바뀌어서 목록 맨 위의 항목이 검색에서 빠져요. 참조 표를 $F$2:$F$5처럼 고정하세요.

자주 하는 실수

FALSE나 0을 빼먹기. FALSE 없는 VLOOKUP과 0 없는 MATCH는 목록이 정렬되어 있다고 가정해서, 오류 대신 비슷한 값을 돌려줄 수 있어요. 정확히 찾을 때는 꼭 입력하세요.
열 번호를 A열부터 세기. VLOOKUP의 col_index_num은 table_array의 첫 번째 열부터 세요. F2:H5에서 가격은 8번째가 아니라 3번째 열이에요.
찾을 범위와 돌려줄 범위의 크기가 다르기. =XLOOKUP(A2,F2:F5,H2:H6)은 #VALUE!를 돌려줘요. 두 범위가 같은 행을 포함하도록 맞추세요.
IFERROR로 모든 문제를 감추기. 조회를 IFERROR로 감싸면 깨진 수식에서 나는 #REF!와 #NAME?까지 감춰져요. "찾지 못함"만 처리하는 XLOOKUP의 네 번째 인수나 IFNA를 쓰세요.

연습

내 참조 표에서 필드 가져오기

직접 참조 표를 만들어 보세요. 제품과 가격, 직원과 부서, 과목 코드와 과목명 등 무엇이든 좋아요. 찾을 열의 값이 한 번씩만 나오는지 확인하세요.
그 옆에 두 번째 표를 만들고, 참조 표에 있는 값 몇 개와 참조 표에 없는 값 하나를 적으세요.
XLOOKUP으로 두 번째 표에 서로 다른 필드 두 개를 가져오세요. 참조 범위는 $로 고정하고, 수식을 아래로 복사하세요.
네 번째 인수를 추가해서, 목록에 없는 값에 #N/A 대신 알아보기 쉬운 메시지가 표시되게 하세요.
조회 하나를 INDEX와 MATCH로, 한 번 더 FALSE를 넣은 VLOOKUP으로 바꿔 쓰고, 세 가지 결과가 같은지 확인하세요.

그다음 아래 시트의 빈 셀 네 개를 채워 보세요.

직접 해 보기4개 중 0개 완료

B2에 XLOOKUP으로 제품 목록(F2:F5)에서 위젯 B를 찾아 H2:H5의 가격을 가져와 보세요.
C2에 VLOOKUP으로 정확히 일치하는 값을 찾아, F2:H5의 두 번째 열인 위젯 B의 분류를 가져와 보세요.
B3에 INDEX와 MATCH로 멀티탭의 가격을 가져와 보세요. 일치 유형은 0이에요.
B4에 목록에 없는 USB 허브의 가격을 찾되, #N/A 대신 없음을 표시해 보세요.

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	F	G	H
1	제품	가격	분류	제품	분류	가격
2	위젯 B			위젯 A	하드웨어	12
3	멀티탭		전자 제품	위젯 B	하드웨어	18
4	USB 허브		#N/A	케이블 타이	액세서리	3
5	케이블 타이	3	액세서리	멀티탭	전자 제품	22

실력 점검

표에서 값을 찾을 때 "정확히 일치"가 무슨 뜻인지 설명할 수 있나요?
따로 떨어진 참조 표에서 가격을 가져오는 XLOOKUP을 쓸 수 있나요?
VLOOKUP 수식을 읽고, 열을 삽입할 때 XLOOKUP이나 INDEX/MATCH가 더 안전한 이유를 설명할 수 있나요?
표에 없는 값을 조회할 때 #N/A를 표시하지 않고 처리할 수 있나요?

도움이 되었나요? 틀린 부분을 발견하셨나요? 피드백 보내기 · admin@arumsoft.com

엑셀 조회 수식: XLOOKUP, VLOOKUP, INDEX/MATCH 사용법

배울 내용

시작하기 전에

함수 연습장

예제 파일