XLOOKUP 수식 만들기
XLOOKUP은 엑셀 2021·Microsoft 365부터 있어요. 왼쪽 열 찾기, 못 찾을 때 값, 마지막 항목 찾기가 함수 하나로 돼요.
자주 쓰는 XLOOKUP
XLOOKUP, 여섯 칸의 뜻과 옛 엑셀 대체 수식
XLOOKUP이 필요한 순간
판매 기록에서 ‘모니터의 가장 최근 판매 금액’을 찾거나, 사원 코드로 그보다 왼쪽 열에 있는 이름을 가져오거나, 못 찾은 칸에 #N/A 대신 ‘없음’을 띄우고 싶을 때 XLOOKUP을 써요. VLOOKUP으로도 비슷한 일을 할 수 있지만 열 번호를 세고, 오류를 감싸고, 마지막 값을 찾는 꼼수를 따로 붙여야 해요. XLOOKUP은 이걸 인수 하나씩으로 해결해서 수식을 처음 보는 사람도 뜻을 읽기 쉬워요.
사용 방법
1단계범위와 못 찾을 때 값 넣기
- 1① 찾을 범위는 한 열만 잡아요. 예시는 제품명이 있는 D2:D11이에요.
- 2② 가져올 범위는 찾을 범위와 행 수를 맞춰요. 예시는 금액이 있는 G2:G11이에요.
- 3③ 못 찾을 때 보여 줄 글자를 적어요. 비워 두면 이 인수가 빠져 #N/A가 그대로 나와요.
2단계검색 방향 바꾸고 결과 보기
- 4④ ‘마지막부터’를 고르면 수식 끝에 -1이 붙어요. 같은 제품이 여러 번 팔렸다면 가장 아래 행, 곧 가장 최근 기록을 가져와요.
- 5⑤ 위 카드는 XLOOKUP, 아래 카드는 옛 엑셀에서 같은 결과를 내는 대체 수식이에요.
두 범위를 따로 주는 원리
XLOOKUP은 찾을 범위에서 위치를 정하고, 가져올 범위의 같은 위치 값을 꺼내요. 두 범위가 따로 놀기 때문에 가져올 열이 왼쪽에 있든 다른 시트에 있든 상관없어요. 예제 데이터에서 모니터는 3·7·11행에 있어서 검색 방향에 따라 결과가 달라져요.
도구는 선택한 값에 따라 꼭 필요한 인수만 붙여요. 일치 방식과 검색 방향이 기본값(0, 1)이면 뒤쪽 인수를 생략하고, 검색 방향만 바꿨다면 =XLOOKUP("모니터",$D$2:$D$11,$G$2:$G$11,"없음",0,-1)처럼 앞 칸을 채워서 순서를 지켜요.
| XLOOKUP 설정 | 도구가 만드는 대체 수식 |
|---|---|
| 정확히 같은 값 | INDEX + MATCH(…, 0) |
| 없으면 바로 작은 값 (-1) | INDEX + MATCH(…, 1), 오름차순 정렬 필요 |
| 없으면 바로 큰 값 (1) | INDEX + MATCH(…, -1), 내림차순 정렬 필요 |
| 와일드카드 (2) | INDEX + MATCH(…, 0), *·?는 MATCH도 인식 |
| 마지막부터 (-1) | LOOKUP(2, 1/(범위=값), 가져올 범위) |
주의할 점
- 대체 수식의 정렬 조건 — XLOOKUP의 근사값 찾기는 정렬이 없어도 되지만 MATCH 1·-1은 정렬된 범위에서만 맞는 답을 줘요. 옛 버전용 수식을 쓸 때는 표를 먼저 정렬하세요.
- 와일드카드 모드에서 * 자체를 찾을 때 — 별표나 물음표를 글자 그대로 찾으려면 앞에 ~를 붙여 ~*처럼 적어야 해요.
- 찾을 값의 따옴표 — 셀 주소나 숫자가 아닌 값은 도구가 큰따옴표로 감싸요. 숫자처럼 보이는 코드(예: 0012)는 숫자로 처리되니, 글자로 찾으려면 직접 "0012"라고 적어 넣으세요.
더 궁금한 점
‘자주 쓰는 XLOOKUP’ 단추는 무엇인가요?
예제 데이터 기준으로 자주 쓰는 다섯 가지 모양을 바로 보여 주는 단추예요. 누르면 그 수식 하나만 카드로 나오고, ‘예제 엑셀로 확인’을 누르면 그 수식이 든 연습 파일을 받을 수 있어요.
검색 방향에 2와 -2도 있다던데 왜 없나요?
2와 -2는 정렬된 범위를 반씩 잘라 가며 찾는 이진 검색이에요. 수만 행에서는 빠르지만 범위가 정렬돼 있지 않으면 틀린 값을 조용히 돌려줘서, 이 도구는 처음부터(1)와 마지막부터(-1)만 골라 쓰게 했어요.
구간 찾기를 할 때 -1과 1 중 무엇을 고르나요?
점수 85를 0·60·70·80·90 기준표에서 찾아 B를 얻으려면 ‘바로 작은 값’(-1)이에요. 배송비처럼 ‘이 무게까지’를 기준으로 위 구간을 골라야 하면 ‘바로 큰 값’(1)을 써요.
XLOOKUP은 VLOOKUP의 단점을 고친 함수예요
XLOOKUP(찾을 값, 찾을 범위, 가져올 범위, [못 찾을 때], [일치 방식], [검색 방향])은 찾을 범위에서 값을 찾아 가져올 범위의 같은 위치 값을 돌려줘요. 앞의 세 개만 필수이고, 나머지는 필요할 때만 넣어요.
VLOOKUP보다 편한 점
- 왼쪽 열도 찾아요 — 가져올 범위를 따로 지정하니 기준 열의 왼쪽 값도 가져와요.
- 열 번호를 세지 않아요 — 중간에 열을 넣어도 수식이 깨지지 않아요.
- 기본이 정확히 일치 — VLOOKUP처럼 마지막 인수를 빠뜨려 엉뚱한 값이 나오는 일이 없어요.
- 못 찾을 때 값 — IFERROR로 감싸지 않아도 "없음" 같은 값을 보여 줘요.
- 마지막부터 찾기 — 검색 방향을 -1로 두면 같은 값이 여러 개일 때 가장 아래(최근) 값을 가져와요.
일치 방식
0은 정확히 같은 값, -1은 없으면 바로 작은 값(점수 구간 → 등급처럼), 1은 없으면 바로 큰 값, 2는 *(여러 글자)와 ?(한 글자)를 쓰는 와일드카드 검색이에요. 구간 찾기를 해도 XLOOKUP은 범위를 정렬해 둘 필요가 없어요.
내 엑셀에 XLOOKUP이 없다면
엑셀 2019 이하에서는 #NAME? 오류가 나요. 이 도구가 함께 보여 주는 INDEX·MATCH 수식을 쓰면 같은 결과를 얻을 수 있어요. 구글 시트는 XLOOKUP을 지원해요. 받은 예제 파일의 XLOOKUP 칸은 엑셀 2021·365에서 열어야 값이 보이고, 옛 버전에서는 아래 INDEX·MATCH 칸으로 확인하세요.
찾을 범위와 가져올 범위는 행 수가 같아야 해요. 표 전체를 표(Ctrl+T)로 만들어 두면 =XLOOKUP(D2,제품표[제품],제품표[분류])처럼 이름으로 쓸 수 있어 데이터가 늘어나도 범위를 고칠 필요가 없어요.
참고한 자료
자주 묻는 질문
결과가 #SPILL!로 나와요.
가져올 범위를 여러 열로 주면 결과가 옆 칸으로 펼쳐져요. 펼쳐질 자리에 다른 값이 있으면 #SPILL!이 나니 오른쪽 칸을 비워 두세요.
숫자를 찾는데 못 찾는다고 나와요.
한쪽은 숫자, 한쪽은 텍스트로 저장된 숫자일 가능성이 커요. ‘엑셀 숫자 텍스트 변환’ 도구로 형식을 맞추거나 찾을 값을 &""로 글자로 바꿔 보세요.
조건 두 개로 찾을 수 있나요?
돼요. =XLOOKUP(1,(B2:B11="서울")*(D2:D11="노트북"),G2:G11)처럼 조건을 곱해 1을 찾으면 두 조건을 모두 만족하는 첫 행 값을 가져와요.