구글 스프레드시트 xlookup 기초 (행/열 조건에 만족하는 값 찾기)

목차

1. 구글 스프레드시트도 xlookup이 가능하다?
2. xlookup이 필요한 경우
3. 예제를 통한 xlookup 실습
   3.1 xlookup 함수 구성
   3.2 xlookup 조건 1가지로 값 찾기
   3.3 xlookup 조건 2가지로 값 찾기 (행/열)
 

1. 구글 스프레드시트도 xlookup이 가능하다?

구글 스프레드시트는 아시다시피 구글에서 무료로 웹 상에서 사용할 수 있게 배포한 소트프웨어라고 보시면 되는데요, 마이크로소프트 엑셀에서 사용하는 웬만한 함수와 기능들을 구글 스프레드시트에서도 대부분 동일하게 활용 가능합니다. 하지만 마이크로소프트의 전략(?)으로 최신 버전인 Microsoft 365(이하 M365)가 아니면 활용할 수 없는 함수들이 있습니다. 그 중 하나인 xlookup에 대해 M365를 구독(구매)하지 않고도 구글 스프레드시트에서 활용할 수 있는 방법에 대해 공유드리겠습니다.

출처: 마이크로소프트 Excel함수(사전순)

 

2. xlookup이 필요한 경우

xlookup 참조함수로 데이터 테이블에서 최소 2가지 조건 이상을 적용해서 값을 찾고자 할 때 활용할 수 있는데요, 보통 많이 쓰이는 경우를 예를들어 설명해 볼게요. 행에서 원하는 조건이 영업이익이고, 열에서 원하는 조건이 2022년도라고 해봅시다. 찾는 값은 C3에 해당되는 1,594 입니다. 사실 그냥 직접 눈으로 가서 찾아도 되지만, 데이터가 많은 경우에는 찾기 복잡하므로 xlookup함수를 사용하면 원하는 조건을 빠르고 정확하게 찾을 수 있겠습니다.
 

 

3. 예제를 통한 xlookup 실습

3.1 xlookup 함수 구성

엑셀의 xlookup함수와 동일한 구성입니다. 총 6가지의 변수가 있습니다.

• 검색_키 : 찾을 조건
  (예시: "영업이익", 2022, G3)
• 조회_범위 : 찾을 범위 (행 또는 열 하나)
• 결과_범위 : 반환할 범위 (찾을 범위와 대칭)
• 누락_값 : 일치 항목이 없을 때 반환하는 값
  (미 지정 시 오류 발생)
• 일치_모드 :
  • 0 : 완전일치(미 지정 시 Default)
  • 1 : 완전일치 또는 검색_키보다 큰 다음값
  • -1 : 완전일치 또는 검색_키보다 작은 다음값
  • 2 : 와일드 카드 일치 항목
• 검색_모드 : 조회 범위에서 검색하는 방법
  • 1 : 첫 번째 항목부터 마지막 항목까지 검색 (미 지정 시 Default)
  • -1 : 마지막 항목부터 첫 번째 항목까지 검색
  • 2 : 범위가 오름차순으로 정렬되었다고 가정하고 바이너리 검색을 사용하여 범위 내 검색
  • -2: 범위가 내림차순으로 정렬되었다고 가정하고 바이너리 검색을 사용하여 범위 내 검색

수식이 생각보다 복잡하긴 하지만 누락_값, 일치_모드, 검색_모드를 생략하고 수식을 입력하셔도 무방 합니다.

 

3.2 xlookup 조건 1가지로 값 찾기

조건 한 가지를 찾으려면 수식 원리에 맞춰서 찾을 범위를 행 또는 열 한가지로 해주면 되는데요, 위의 예제에서 2022년에서의 "순이익"이라는 조건의 값을 찾아 보겠습니다. 찾을 값은 B7(순이익)으로 했고요, 조회_범위는 A2:A4영역으로 설정, 그리고 결과_범위는 조회_범위와 대칭되게 2022년 영역인 C2:C4로 설정 했습니다. 우측의 예시를 보시면 수식의 배치와 구조를 이해하시는 데 도움이 될 겁니다. 결과값은 보시는 바와 같이 1,008로 올바르게 나온 것을 확인하실 수 있습니다.
 

 

이번엔 2021년도의 "매출액"을 찾아보겠습니다. 위의 방법과 동일하게 찾을 값을 B7(매출액)으로 합니다. (B7의 값을 기존 순이익에서 매출액으로 변경해 주었습니다) 그리고 조회_범위는 동일하게 A2:A4영역 그리고 결과_범위는 2021년 영역인 B2:B4 영역으로 설정했습니다. 결과값은 10,058로 올바르게 출력된 것을 보실 수 있습니다.
 

3.3 xlookup 조건 2가지로 값 찾기 (행/열)

행/열 조건 2가지를 활용해서 원하는 값을 찾아 보겠습니다. 이른바 xlookup중첩/xlookup중복 이라고도 합니다. 위의 예시에다가 추가적으로 원하는 조건을 2가지를 G2, G3열에 미리 입력해 놓았습니다. 해당 값들은 언제든지 바꿔서 원하는 조건으로 바꿀 수 있죠. 그리고 G5에다가 위의 수식을 활용하여 중첩해보겠습니다.

=xlookup(검색_키1,조회_범위1,xlookup(검색_키1,조회_범위2,결과_범위))
위와 같은 수식 구성을 가지게 되는데요, 실제로 한번 입력 해보겠습니다.
 

 

실제로 찾는 값인 1,594가 나왔습니다. 조건 1과 조건 2의 순서는 바뀌어도 상관 없습니다. 다만 조건 1의 조회_범위와 조건 2의 조회_범위가 섞이지만 않는다면 수식 오류는 나지 않을 것입니다. 여기서 G2, G3값의 조건들만 바꿔서 값이 바뀌는지 확인해 보겠습니다. 아래에는 이번엔 2021년도의 순이익으로 조건만 바꿨습니다. 432로 정확하게 나온 것을 확인하실 수 있습니다.

 

▼ 함께 보면 좋은 글

구글 스프레드시트 xlookup 심화 (다중 조건 만족 값 찾기)

 

구글 스프레드시트 Lambda 함수 (이름이 지정된 함수)