구글 스프레드시트 XLOOKUP 사용법: 왼쪽 조회·없음 처리·마지막 값 찾기
XLOOKUP은 찾을 범위와 가져올 범위를 따로 지정해, 검색 열의 왼쪽·오른쪽 어디에 있는 값도 정확히 반환합니다. 가장 자주 쓰는 기본형은 =XLOOKUP(H2,A2:A6,C2:C6,"없음")입니다.
이 수식은 H2의 주문번호를 A2:A6에서 찾고 같은 행의 C열 상태를 보여줍니다. 일치하는 주문번호가 없으면 #N/A 대신 없음을 표시합니다.
아래에서는 중복 주문번호가 있는 주문표를 기준으로 기본 조회, 왼쪽 조회, 마지막 상태 선택, 여러 열 반환, #N/A·#REF! 복구 순서를 설명합니다.
먼저 고를 방식
- 한 주문의 값 하나: 기본 XLOOKUP
- 검색 열 왼쪽의 값: 결과 범위를 따로 지정
- 중복 키의 마지막 행: 검색 모드
-1 - 일치하는 여러 행 전체: XLOOKUP 대신 FILTER
XLOOKUP은 언제 쓰나요?
코드나 이름 하나를 찾아 같은 행의 값을 가져오되, 열 위치와 누락값 처리를 직접 정하고 싶을 때 XLOOKUP이 알맞습니다. 주문번호로 상태를 찾거나 사번으로 부서를 가져오는 작업처럼 한 키에 대응하는 결과가 필요할 때 사용합니다.
| 구분 | XLOOKUP | VLOOKUP |
|---|---|---|
| 검색 열 위치 | 조회 범위를 한 행·열로 따로 지정 | 표 범위의 첫 열에서 검색 |
| 정확히 일치 | 기본값 0 | FALSE를 지정 |
| 값이 없을 때 | 누락값 인수에 문구 지정 | IFNA·IFERROR 등을 함께 사용 |
| 중복 키 검색 방향 | 첫 행·마지막 행 선택 가능 | 기본적으로 위에서 첫 일치 |
기존 VLOOKUP 수식의 오류를 고쳐야 한다면 VLOOKUP #N/A·#REF! 원인별 복구 방법을 함께 참고하세요. 새 표에서 열 방향 제약을 줄이고 싶다면 XLOOKUP이 더 단순합니다.
수식 구조는 어떻게 읽나요?
결과는 검색 키, 조회 범위, 결과 범위의 세 필수 인수와 세 선택 인수로 결정됩니다.
| 인수 | 이 글의 값 | 역할 |
|---|---|---|
| 검색 키 | H2 | 찾을 주문번호 |
| 조회 범위 | A2:A6 | 검색할 한 행 또는 한 열 |
| 결과 범위 | C2:C6 | 같은 위치에서 가져올 값 |
| 누락값 | "없음" | 일치 항목이 없을 때 표시 |
| 일치 모드 | 0 | 정확히 같은 값 검색 |
| 검색 모드 | 1 또는 -1 | 위에서부터 또는 아래에서부터 검색 |
2는 오름차순, -2는 내림차순 정렬이 먼저 필요합니다.기본 정확히 찾기는 어떻게 하나요?
검색 키가 있는 셀, 주문번호 범위, 상태 범위를 차례로 지정하면 됩니다. 주문표의 A열은 주문번호, B열은 고객명, C열은 상태이며 A2:A6에는 ORD-102가 두 번 들어 있습니다.
| 행 | 주문번호 | 고객명 | 상태 |
|---|---|---|---|
| 2 | ORD-101 | 김하늘 | 결제완료 |
| 3 | ORD-102 | 이준호 | 배송중 |
| 4 | ORD-103 | 박서연 | 결제완료 |
| 5 | ORD-102 | 이준호 | 반품접수 |
| 6 | ORD-104 | 최민석 | 주문취소 |
H2가 ORD-103이면 결과는 결제완료입니다. H2가 ORD-999라면 일치 항목이 없으므로 누락값 인수에 적은 없음이 표시됩니다.
왼쪽 열의 값도 가져올 수 있나요?
결과 범위를 검색 열의 왼쪽에 두어도 됩니다. 고객명 박서연을 B2:B6에서 찾고 왼쪽 A열의 주문번호를 가져오려면 다음 수식을 사용합니다.
H5가 박서연이면 주문번호 ORD-103이 반환됩니다. 표 전체와 열 번호를 묶는 대신 조회 범위와 결과 범위를 따로 지정하므로 열을 삽입해도 반환할 열 번호를 다시 계산할 필요가 없습니다.
중복 키의 첫 값과 마지막 값은 어떻게 고르나요?
검색 모드 1은 위에서 첫 일치, -1은 아래에서 첫 일치를 반환합니다. ORD-102가 3행과 5행에 있을 때 결과가 다음처럼 달라집니다.
결과: 배송중
결과: 반품접수
-1은 날짜가 가장 최신인 행이 아니라 표에서 가장 아래에 있는 일치 행을 뜻합니다. 최신 상태가 필요하면 날짜 기준으로 행 순서를 먼저 정리하세요.여러 열을 한 번에 반환할 수 있나요?
결과 범위를 여러 열로 넓히면 일치한 행의 값이 옆 셀로 펼쳐집니다. ORD-103의 고객명, 상태, 금액을 한 번에 가져오려면 결과 범위를 B2:D6으로 지정합니다.
결과는 박서연 · 결제완료 · ₩27,000이 세 셀에 나뉘어 표시됩니다. 수식 셀 오른쪽에 기존 값이 있으면 배열이 펼쳐질 수 없으므로 #REF!가 발생합니다.
#N/A와 #REF!는 어떻게 해결하나요?
오류 이름보다 먼저 검색 키, 범위 크기, 출력 공간을 차례로 확인하세요.
| 증상 | 먼저 확인 | 원인 | 복구 |
|---|---|---|---|
#N/A | 검색 키가 범위에 있는지 | 정확히 같은 값이 없음 | 키를 고치거나 누락값 인수 지정 |
#N/A | 두 범위의 행·열 수 | 조회·결과 범위 크기가 다름 | A2:A6과 C2:C6처럼 맞춤 |
#REF! | 결과 셀의 오른쪽 | 배열 출력 위치에 기존 값이 있음 | 겹치는 셀을 비우거나 수식 위치 이동 |
| 첫 값만 나옴 | 키 중복과 검색 모드 | 기본 검색이 위에서 아래 방향 | 마지막 행이면 검색 모드 -1 |
| 엉뚱한 근사값 | 일치 모드와 정렬 | 1·-1 또는 정렬 없는 이진 검색 | 정확히 찾기는 일치 모드 0 |
조건에 맞는 행을 하나가 아니라 모두 가져와야 한다면 FILTER 함수의 다중 조건과 범위 오류 해결 방법이 목적에 더 맞습니다.
PC·Android·iPhone에서 무엇이 다른가요?
XLOOKUP 수식과 계산 결과는 기기에 따라 달라지지 않고, 수식을 입력하고 범위를 선택하는 화면만 다릅니다.
| 환경 | 입력 위치 | 확인할 점 |
|---|---|---|
| PC 웹 | 셀 또는 수식 입력줄 | 열 범위와 펼쳐질 셀을 한 화면에서 확인하기 쉬움 |
| Android 앱 | 셀을 선택한 뒤 수식 입력줄 | 범위 선택 손잡이가 정확한 행까지 포함하는지 확인 |
| iPhone·iPad 앱 | 셀을 선택한 뒤 수식 입력줄 | 여러 열 반환 시 오른쪽 출력 셀이 비어 있는지 확인 |
긴 수식과 여러 범위를 처음 설정할 때는 PC 웹이 편합니다. 모바일에서는 수식 결과를 확인하거나 검색 키 셀의 값만 바꾸는 방식이 실수를 줄입니다.
공식 도움말과 관련 기능
한 키에 대응하는 한 행을 찾는 작업은 XLOOKUP, 같은 조건의 여러 행을 모으는 작업은 FILTER로 나누면 수식 목적이 선명해집니다. 중복 키에서 마지막 행이 필요하면 검색 모드 -1을 사용하되, 표의 아래쪽이 실제 최신 순서인지 먼저 확인하세요.