특정 조건에 일치하는 데이터 찾기 xlookup 함수 (vlookup 보다 편한)

아마도 실무에서 가장 많이 사용하는 함수는 vlookup이라는 생각이 듭니다.

특정 조건에 일치하는 데이터를 찾아야 하는 경우가 생각보다 많기 때문입니다.

• 특정 상품의 가격
• 특정 상품의 재고 현황
• 특정 상품의 매출 합계 등

하지만 실무에서 생각보다 vlookup이 불편해서 여러 함수를 섞어서 사용한다든가, Index/Match 함수를 사용하는 경우가 많습니다.

 

이러한 단점들을 개선하여 간편하게 사용할 수 있는 xlookup 함수가 있어서 소개드리고자 합니다.

vlookup을 사용하시던 분들도 앞으로는 편하게 xlookup을 사용하시면 좋을 것 같아요.

xlookup 함수

반응형

 

 

xlookup 함수 소개

=XLOOKUP(찾는 값, 찾는 범위, 결과 범위, [데이터가 없는 경우], [일치 옵션], [검색 옵션])

• <필수> 찾는 값: 특정 텍스트를 입력해도 되고, 셀을 입력해도 됨 (텍스트는 입력 시, "" 사용)
• <필수> 찾는 범위: 찾고자 하는 데이터가 포함된 범위 (위 찾는 값이 포함되어 있는 범위)
• <필수> 결과 범위: 결과 값으로 출력하고자 하는 데이터가 포함된 범위
• [옵션] 데이터 없는 경우: 데이터가 없을 경우 #N/A가 출력되는데, 입력 시 원하는 값으로 출력
• [옵션] 일치 옵션
  • 0: 정확하게 일치 (기본값)
  • 1: 정확하게 일치하거나 검색 값보다 다음으로 큰 값을 출력
  • -1: 정확하게 일치하거나 검색 값보다 다음으로 작은 값을 출력
  • 2: 와일드카드 사용
• [옵션] 검색 옵션
  • 1: 위에서 아래로 검색 (기본값)
  • -1: 아래에서 위로 검색
  • 2: 오름차순으로 정렬된 조회 범위에서 binary 검색
  • -2 : 내림차순으로 정렬된 조회 범위에서 binary 검색

 

 

옵션 값들이 있어서 다소 복잡해 보이지만, 필수 값만 지정해도 사용하는데 전혀 문제가 없습니다.

아래 예시들을 통해 조금 더 쉽게 설명해 드릴게요.

728x90

 

 

[기초] 일치하는 데이터 찾기 

우선 필터를 사용하시는 분들도 많이 있는 걸로 알고 있는데, 메타데이터 시트를 여러 사람이 공유해서 사용한다고 하면 필터를 풀어줘야 하는 번거로움이 있어서 반드시 새로운 시트를 만들어서 사용하는 걸 추천합니다!

좌측 하단 새로운 시트 만들기

 

새로운 시트에서는 Book ID를 이용하여 도서명, 재고 상태를 파악할 수 있는 함수를 만들어 볼게요.

이전 글에서 설명했듯이 보기 좋게 서식 적용 및 빈칸을 삭제해 주면 좋습니다.

• 사용자가 조금 더 쉽게 인지할 수 있도록 Book ID에는 사용자가 변경 가능하다는 의미로 노란 배경색을 적용했어요.
• Title 및 Availabilty Status에는 xlookup 함수를 적용하여 사용자 변경이 불가능하므로 회색 배경색을 적용했어요.

데이터 테이블 만들기

 

먼저 Title을 자동으로 불러오도록 xlookup 함수를 적용해 볼게요.

1) B2 셀을 클릭하고 아래 수식을 입력하신 후, 찾는 값이 있는 A2 셀을 마우스로 클릭해 주세요.

=xlookup( 

xlookup 예시

 

2) A행의 데이터를 불러올 생각이므로, F4를 세 번 눌러서 $A2로 변경해 두시면 좋습니다.

3)  , 를 입력한 후 하단에 Sample_metadata 시트를 클릭해 주세요.

xlookup 예시

 

4) 찾는 값이 포함되어 있는 범위를 선택하기 위해서, 파란색으로 칠해진 A행을 클릭한 후 F4를 눌러서 절대참조로 변경해 주세요.

xlookup 예시

 

5)  , 를 입력한 후 결과 범위를 선택하이 위해서, 파란색으로 칠해진 B행을 클릭한 후 F4를 눌러서 절대참조로 변경해 주세요.

xlookup 예시

 

6) 엔터를 누르시면 함수가 적용된 결과 값을 확인할 수 있습니다.

xlookup 예시

 

7) B2 셀에 입력된 수식을 복사하여 C2셀에 붙여 넣기를 한 후, 결과 범위를 Availability Status가 적힌 L행으로 변경해 주세요.

xlookup 예시

 

8) 나머지 셀에도 수식을 복사하여 붙여 넣기 하면 완성입니다.

xlookup 예시

 

9) Book ID 203, 205는 메타데이터에 존재하지 않아서 #N/A로 에러처리가 되는데, 수식에다가  ,  ""만 추가해 주면 빈칸으로 나오게 수정이 가능합니다.

xlookup 예시

10) 완성

 

 

[응용] 도서 검색 찾기 

위와 같은 xlookup 함수를 활용하여 간단한 도서 검색 시스템을 만들어 볼 수도 있습니다.

xlookup 예시

도서명을 검색하여 검색어가 포함된 도서의 정보를 간단하게 불러오는 예시입니다.

(참고로 간단한 예시이기에 검색어가 1개 이상의 결과값을 출력할 경우, 최상단 값만 불러오도록 되어있습니다.)

• Title

=xlookup("*"&$A$2&"*",Sample_metadata!$B:$B,Sample_metadata!$B:$B,,2)

• Author

=xlookup("*"&$A$2&"*",Sample_metadata!$B:$B,Sample_metadata!$C:$C,,2)

• Genre

=xlookup("*"&$A$2&"*",Sample_metadata!$B:$B,Sample_metadata!$D:$D,,2)

• Publisher

=xlookup("*"&$A$2&"*",Sample_metadata!$B:$B,Sample_metadata!$H:$H,,2)

• Format

=xlookup("*"&$A$2&"*",Sample_metadata!$B:$B,Sample_metadata!$J:$J,,2)

• Price

=xlookup("*"&$A$2&"*",Sample_metadata!$B:$B,Sample_metadata!$K:$K,,2)

• Availability Status

=xlookup("*"&$A$2&"*",Sample_metadata!$B:$B,Sample_metadata!$L:$L,,2)

 

기초 예시와는 다르게 와일드카드 옵션을 사용했습니다.

찾는 값이 "*"&$A$2&"*"로 변경되었는데, A2셀 앞뒤로 *를 붙인다는 의미입니다.

위 예시 같은 경우, 실제 검색은 *beaultiful*로 검색이 되며 beautiful이 포함된 모든 텍스트를 검색한다는 의미입니다.

또한 위와 같이 와일드카드 옵션을 적용하기 위해 일치 옵션에 2를 입력했습니다.

 

간단한 함수이지만 상황에 따라 무궁무진하게 활용이 가능하므로, 직접 사용해 보세요!