엑셀 XLOOKUP 함수 사용법을 찾고 계신가요? 이 글에서는 VLOOKUP 함수 한계를 극복할 수 있는 XLOOUP 함수 활용법을 소개합니다. XLOOKUP 기본 사용법부터 와일드카드, 배열, 검색 방향, 오류값 설정 방법 5가지 주요 활용법을 총정리해보겠습니다.
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
엑셀 XLOOKUP 함수 사용법 5가지
XLOOKUP 함수는 마이크로소프트 365버전 및 엑셀 2021 이후 버전에서만 사용할 수 있습니다.
VLOOKUP 함수 사용이 불가한 상황에서는 INDEX MATCH 함수를 사용해야 했습니다. 하지만 XLOOKUP 함수만 있다면 1개의 함수로 해결이 가능합니다.
▶︎엑셀 조건에 맞는 값 모두 가져오기 | INDEX MATCH 중첩
1.엑셀 XLOOKUP 함수 기본 사용법
엑셀 XLOOKUP 함수 인수는 아래와 같이 구성됩니다. 찾을값, 찾을범위, 반환범위 3개만 입력하면 값을 찾아올 수 있습니다. 일단 바로 아래에서 XLOOKUP 에 관한 기본 정보를 훑어보세요.
| ▶︎= XLOOKUP ( 찾을값, 찾을범위, 반환범위, [N/A값], [일치옵션], [검색방향] ) |
|---|
| ▶︎찾을값, 찾을범위, 반환범위: 필수 입력 ▶︎[N/A값]: 선택 입력, 오류값 표시할 텍스트 입력하기, 입력 안하면 #N/A가 반환됨 ▶︎[일치옵션]: 선택 입력, 일치 유형 지정하기 0 입력 시: 정확히 일치하는 값 반환 -1 입력 시: 정확히 일치하는 값 찾고, 없으면 다음으로 작은 항목 반환 1 입력 시: 정확히 일치하는 값 찾고, 없으면 다음으로 큰 항목 반환 2 입력 시: *, ?, ~ 에 해당되는 와일드카드에 일치하는 값 반환 ▶︎[검색방향]: 선택 입력, 검색할 방향 지정하기, 입력 안하면 위에서부터 검색함 1 입력 시: 위에서부터 검색함 -1 입력 시: 아래에서부터 검색함 2 입력 시: 오름차순으로 정렬된 찾을범위 이진검색, 정렬 안할 시 잘못된 결과 반환 -2입력 시: 내림차순으로 정렬된 찾을범위 이진검색, 정렬 안할 시 잘못된 결과 반환 |
이제 XLOOKUP 기본 사용법을 알아보겠습니다.
VLOOKUP 함수는 찾을값이 반드시 제일 왼쪽 첫 번째 열에 위치해야 합니다. 하지만 XLOOKUP 함수는 몇 번째 열에 위치 하던 값을 찾을 수 있습니다.
아래 사진을 보면 고객아이디 및 고객사명에 따른 판매제품 데이터가 나와 있습니다. 고객사명에 따른 고객아이디를 찾아온다고 해보겠습니다.
고객아이디 아래 셀에 =XLOOKUP(A12,C2:C9,B2:B9) 라고 입력합니다. 첫 번째 인수는 찾을 값인 ‘최고문구’ 셀입니다. 두 번째 인수는 ‘최고문구’ 셀이 포함된 찾을 범위입니다. 세 번째 인수는 반환할 값이 포함된 반환 범위입니다.
함수 입력 후 엔터키를 누르면 ‘최고문구’ 에 해당되는 고객아이디인 ‘ab15’ 결과값이 나옵니다.
2.XLOOKUP 배열
XLOOKUP 함수는 범위로 결과값을 찾아올 수도 있습니다. 각 고객아이디에 해당되는 고객사명, 판매제품, 제품단가를 한 번에 찾아온다고 해보겠습니다.
고객사명 글자 아래 셀에 =XLOOKUP(D12,B2:B9,C2:E9) 라고 입력합니다. 첫 번째 인수는 찾을값인 ‘ab11’ D12셀입니다.
아래 셀에도 자동채우기를 해서 완성을 시킬 겁니다. 따라서 열만 고정시키고 행은 이동시킬 수 있도록 해야 합니다. 열만 고정하려면 F4 키를 3번 누르면 됩니다. 그럼 D12 가 $D12 로 바뀌며, 열만 고정이 된 것입니다.
XLOOKUP 함수 내 두 번째 인수로 ab11 셀이 포함되어 있는 고객아이디 전체 범위를 선택합니다. 이번에도 F4 키를 3번 눌러서 열만 고정합니다. 그럼 B2:B9 가 $B2:$B9 로 바뀝니다.
세 번째 인수로 반환 범위를 선택합니다. 고객사명, 판매제품, 제품단가 전체 범위를 선택하면 되는데요. C2에서 E9까지의 범위를 모두 선택합니다. 이번에도 F4 키를 3번 눌러서 열만 고정 시킵니다. 그럼 C2:E9 가 $C2:$E9 로 바뀝니다.
함수를 모두 입력하고 엔터키를 누릅니다. 그럼 고객사명, 판매제품, 제품단가가 한 번에 찾아와집니다. 아래 셀에도 자동채우기를 하면 한번에 결과값이 찾아와집니다.
3.XLOOKUP 검색 방향
XLOOKUP 함수는 기본적으로 위에서부터 값을 찾습니다. 하지만 우리가 따로 지정을 해서 아래에서부터 값을 찾도록 할 수 있습니다.
우정문구의 고객아이디를 아래에서부터 검색한 후 결과값을 찾아와보도록 해볼게요.
=XLOOKUP(A17,C2:C9,B2:B9,”오류”,0,-1) 라고 입력합니다. 첫 번째 인수는 찾을 값, 두 번째 인수는 찾을 범위, 세 번째 인수는 반환 범위입니다.
그리고 네 번째 인수는 오류값이 나왔을 때 표시할 텍스트 내용입니다. 저는 예시로 “오류”라고 입력해보았습니다.
다섯 번째 인수는 일치 여부입니다. 정확히 일치하는 값을 찾으려면 0을 입력하면 됩니다.
여섯 번째 인수가 바로 검색 방향입니다. 입력을 안하면 기본적으로 위에서부터 검색을 합니다. -1이라고 입력하면 범위의 맨 아래에서부터 값을 찾습니다.
위 예시의 경우 ‘우정문구’가 범위 내에 2개 있습니다. 근데 밑에서부터 값을 찾으므로 맨 아래에 있는 ‘우정문구’ 에 해당되는 고객아이디인 ‘ab11’ 이 결과값으로 나온 것입니다.
최근 날짜를 찾아야 하는 경우 검색 방향을 -1로 지정하면 값을 쉽게 찾을 수 있습니다.
4.XLOOKUP 오류값
오류가 나면 #N/A 표시가 되는데요. #N/A 대신 내가 원하는 텍스트 등으로 표시되도록 지정할 수 있습니다.
VLOOKUP 함수에서는 IFERORR 함수를 중첩해서 오류값을 지정해야 합니다. 하지만 XLOOKUP 함수는 인수 내에서 간편하게 오류값을 지정할 수 있습니다.
오류값이 있는 경우 ‘오류’ 라고 표기가 되게 지정해보겠습니다. =XLOOKUP(D17,C2:C9,B2:B9,”오류”) 라고 입력합니다.
네 번째 인수에 오류값이 나왔을 때 표시하고 싶은 텍스트를 입력합니다. 예시로 저는 ‘오류’ 라고 입력했습니다. 이 때 반드시 큰 따옴표 안에 ‘오류’ 라고 입력을 해야 합니다.
함수를 모두 입력하고 엔터키를 누릅니다. 찾을 범위 안에 찾을 값이 없다면 위 사진처럼 ‘오류’ 라고 표시가 됩니다.
5.XLOOKUP 와일드카드
XLOOKUP 함수는 와일드카드 사용도 가능합니다.
와일드카드란 다음과 같은 의미입니다. ‘*아이’ 라고 입력 했다면 ‘예쁜아이’, ‘아이조아’, ‘큰아이’ 처럼 글자개수와 관계 없이 ‘아이’가 들어간 모든 값을 반환합니다. ‘?아이’ 라고 입력 했다면 ‘큰아이’, ‘이아이’ 처럼 ?가 있는 글자 개수 및 ? 위치에 해당되는 값을 반환합니다.
예시로 ‘*등문구’ 라고 입력을 해보겠습니다. 즉, ‘등문구’ 라는 텍스트가 들어간 값을 찾아오는 것입니다. 다음과 같이 함수를 입력합니다.
=XLOOKUP(G17,C2:C9,B2:B9,“오류”,2)
다섯번째 인수로 2를 입력하면 와일드카드로 지정된 값을 반환합니다. 검색 방향을 따로 지정하지 않았으므로 기본적으로 위에서부터 값을 찾습니다.
위에서부터 ‘등문구’가 들어간 값을 찾으면 ‘일등문구’ 가 나옵니다. 일등문구에 해당되는 고객아이디는 ‘ab17’ 입니다. 따라서 결과값으로 ‘ab17’이 나온 것입니다.
<결론>
- XLOOKUP 함수
- = XLOOKUP ( 찾을값, 찾을범위, 반환범위, [N/A값], [일치옵션], [검색방향] )
| ▶︎아래는 같이 보면 좋은 글입니다. 엑셀 XLOOKUP 함수 중첩 | SUM 함수 중첩 엑셀 VLOOKUP 다른 시트 값 가져 오기 방법 VLOOKUP 함수 오류 6가지 해결법 |
이상 엑셀 XLOOKUP 함수 사용법에 관한 글이었습니다.