XLOOKUP 함수란?
=XLOOKUP(찾을값, 조회 범위, 반환 범위, [찾지 못할 때], [일치 모드], [검색 방향])XLOOKUP은 한 범위(조회 범위)에서 값을 찾아, 다른 범위(반환 범위)의 같은 위치에 있는 값을 가져오는 함수입니다. VLOOKUP과 하는 일은 같지만, 열번호를 세는 대신 가져올 범위를 직접 지정합니다.
그래서 찾는 열의 왼쪽에 있는 값도 가져올 수 있고, 표 중간에 열을 끼워 넣어도 엉뚱한 열을 가져오지 않습니다. 기본이 정확히 일치라서, VLOOKUP처럼 마지막 인수를 빠뜨려 틀린 값을 얻는 일도 없습니다.
XLOOKUP은 Microsoft 365와 Excel 2021 이후 버전에 있습니다. Excel 2016과 2019에는 없으니, 파일을 주고받을 상대의 버전을 먼저 확인하세요.
인수 설명
- 찾을값
- 조회 범위에서 찾을 값입니다. 텍스트는 대소문자를 구분하지 않습니다.
- 조회 범위
- 찾을값을 찾아볼 범위입니다. 한 열(또는 한 행)로 잡습니다.
- 반환 범위
- 결과를 가져올 범위입니다. 조회 범위와 칸 수가 같아야 하며, 다르면 #VALUE! 오류가 납니다. 조회 범위의 왼쪽에 있어도 됩니다.
- 찾지 못할 때
- 생략할 수 있습니다. 값을 찾지 못했을 때 #N/A 대신 보여 줄 값입니다. "없음"이나 0처럼 넣습니다.
- 일치 모드
- 생략하면 0(정확히 일치)입니다. -1은 같은 값이 없으면 바로 작은 값을, 1은 바로 큰 값을 고릅니다. 2는 * 와 ? 를 와일드카드로 써서 찾습니다.
- 검색 방향
- 생략하면 1(처음부터 끝으로)입니다. -1은 끝에서부터 찾으므로 같은 값이 여러 개일 때 마지막 것을 가져옵니다. 2와 -2는 정렬된 범위에서 쓰는 이진 검색입니다.
예제
예제 1. 상품명으로 가격 찾기
=XLOOKUP("계란", B2:B9, C2:C9)B열에서 계란을 찾고, C열의 같은 위치 값을 가져옵니다. 결과는 6900입니다. 이 페이지의 표가 처음 열렸을 때의 상태입니다.
예제 2. 왼쪽에 있는 값 가져오기
=XLOOKUP("계란", B2:B9, A2:A9)반환 범위를 찾는 열의 왼쪽(A열)으로 잡으면 상품코드 P004를 가져옵니다. VLOOKUP은 첫 열의 오른쪽만 가져올 수 있어서 이렇게 하려면 표의 열 순서를 바꿔야 합니다.
예제 3. 찾지 못하면 "없음" 보여 주기
=XLOOKUP("수박", B2:B9, C2:C9, "없음")수박은 표에 없으므로 #N/A 대신 네 번째 인수인 "없음"이 나옵니다. VLOOKUP에서는 IFERROR로 감싸야 했던 일을 인수 하나로 처리합니다.
자주 하는 실수
조회 범위와 반환 범위의 크기가 다르다
B2:B9에서 찾고 C2:C8에서 가져오면 칸 수가 맞지 않아 #VALUE! 오류가 납니다. 위 표에서 반환 범위의 아래쪽 점을 한 칸 올려 보세요. 두 범위는 같은 행에서 시작하고 같은 행에서 끝나야 합니다.
반환 범위를 표 전체로 잡았다
반환 범위를 C2:D9처럼 여러 열로 잡으면 그 행의 값이 여러 칸에 걸쳐 함께 나옵니다. 결과가 펼쳐질 자리에 이미 다른 값이 있으면 #SPILL! 오류가 납니다. 값 하나만 필요하면 한 열만 잡으세요.
와일드카드가 동작하지 않는다
"계*"처럼 찾으려면 일치 모드를 2로 해야 합니다. 기본값 0에서는 와일드카드로 찾지 않습니다.
상대방 엑셀에서 수식이 깨진다
Excel 2016과 2019에는 XLOOKUP이 없습니다. 이 버전을 쓰는 사람과 파일을 주고받는다면 VLOOKUP이나 INDEX와 MATCH 조합을 쓰는 편이 안전합니다.
자주 묻는 질문
- VLOOKUP 대신 XLOOKUP을 써야 하나요?
- 파일을 쓰는 사람이 모두 Microsoft 365나 Excel 2021 이후 버전이라면 XLOOKUP이 더 편하고 실수도 적습니다. 그렇지 않다면 모든 버전에서 되는 VLOOKUP이나 INDEX와 MATCH를 쓰세요.
- 같은 값이 여러 개일 때 마지막 값을 가져오려면?
- 검색 방향을 -1로 하면 아래에서부터 찾으므로 마지막에 나온 값을 가져옵니다. 위 표에서 검색 방향을 바꿔 보면 비교 순서가 거꾸로 바뀌는 것을 볼 수 있습니다.
- 일치 모드 -1이나 1을 쓸 때 정렬해야 하나요?
- 아닙니다. 처음부터 끝까지 모두 비교해서 가장 가까운 값을 고르므로 정렬하지 않아도 됩니다. 정렬이 필요한 것은 검색 방향을 2나 -2(이진 검색)로 할 때입니다.
- #N/A 대신 빈칸을 보여 주려면?
- 찾지 못할 때 인수에 빈 문자열("")을 넣으면 됩니다. =XLOOKUP(F2, B2:B9, C2:C9, "")