엑셀 XLOOKUP 함수 완전 정복: VLOOKUP의 진화형!

728x90

엑셀에서 데이터를 찾고 연결하는 대표 함수로는 VLOOKUP, HLOOKUP, INDEX, MATCH 등이 있죠. 그런데 2020년 이후 등장한 XLOOKUP 함수는 이 모든 단점을 보완하면서 강력하고 유연한 기능을 제공합니다.

이 글에서는 XLOOKUP 함수의 기본 사용법부터 실무 예제까지 정리해 드릴게요!

​

​

🔍 XLOOKUP이란?

XLOOKUP 함수는 엑셀에서 수직/수평 방향 관계없이 원하는 값을 정확하게 찾아주는 함수입니다. VLOOKUP의 한계를 극복한 차세대 함수로, Microsoft 365와 Excel 2021 이상에서 사용할 수 있어요.

​

​

📌 기본 구문:

excel

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: 찾고자 하는 값
• lookup_array: 찾을 범위 (예: ID 리스트)
• return_array: 반환할 범위 (예: 이름 리스트)
• if_not_found (선택): 값을 못 찾았을 때 표시할 내용
• match_mode (선택): 일치 방식 (0: 정확히, -1: 이하 값, 1: 이상 값, 2: 와일드카드)
• search_mode (선택): 검색 방향 (1: 처음부터, -1: 끝부터)

​

🎯 XLOOKUP vs VLOOKUP 비교

기능	VLOOKUP	XLOOKUP
검색 방향	오른쪽만	양방향 가능
열 번호 필요 여부	O	X
못 찾았을 때 처리	에러 (#N/A)	사용자 지정 가능
정렬 필요 여부	O (근사값일 때)	X
배열 반환	불가	가능 (다차원 가능)

예시

 

빨간색은 찾을값

초록색은 찾을값이 있는 열

파란색은 반환하는 값을 의미해요.

여기서 중요한 것은, "$(F4키)"에 대해 살짝 알려드리자면, 행열을 고정한다는 것을 의미해요.

​

예를들어 $I3는 I라는 열은 고정하지만 4라는 행은 고정한다는 의미에요.

만약 J3 행에서 ctrl+C를 누르고 J4에 붙여넣기한다면 위에 보이는 함수는 똑같지만 아래와 같은 값이 붙을꺼에요

"=XLOOKUP($I4,$D:$D,$E:$E)"

​

또한 I3행을 복사해서 H3에 붙여넣기 한다면, "=XLOOKUP($I3,$D:$D,$E:$E)" 와 같은 함수가 그대로 나타날텐데,

이는 I행에서 H행으로 이동했지만, "$"이 고정으로 인해 I값은 변하지 않는다는 의미지요.

​

위의 예시에서 보면 A가 두개가있는 것을 볼 수 있는데, xlookup을 위의 예시처럼 사용하면 기본적으로 위에서부터 값을 찾아와요.

만약에 아래서부터 불러오고싶다면, 아래 처럼 입력하시면돼요.

​

 

=XLOOKUP($I3,$D:$D,$E:$E,(빈칸),(빈칸),-1)

이러면 아래에서부터 불러오는 것도 가능하답니다.

​

그런데 만약 아래 사진처럼 A~D가 섞여있다면 다중 조건 xlookup을 활용해서 이용하시면 돼요.

엑셀에서 조건이 두 개 이상일 때 원하는 값을 찾아야 하는 경우, 보통 INDEX+MATCH나 배열 수식을 사용했어야 했죠. 하지만 이제는 XLOOKUP 함수 하나로도 다중 조건 검색이 가능합니다!

=XLOOKUP(1,($D$3:$D$9=$I3)*($C$3:$C$9=$H3),$E$3:$E$9)

하나하나 뜯어보면, 이렇게 동작합니다:

• ($D$3:$D$9=I3)
• → 코드열이 I3 셀 값과 일치하는 TRUE/FALSE 배열 생성
• ($C$3:$C$9=H3)
• → 성별열이 H3 셀 값과 일치하는 TRUE/FALSE 배열 생성
• 이 둘을 * 곱하면, AND 조건에 해당하는 항목은 1, 아니면 0이 됩니다.
• 최종적으로 XLOOKUP(1, …)는 조건에 동시에 맞는 첫 번째 행을 찾아 $E$3:$E$9 범위에서 값을 반환합니다.

여기서 1은 True를 의미합니다.

참고로 0은 False를, "+" 는 or을 의미합니다.

​

모르는 내용이 있으시다면, 댓글로 달아주세요.