[ 엑셀 Tip ] xlookup 함수 활용법

xlookup 함수를 활용한 다양한 활용법을 알아보도록 하겠습니다.

정말 자주 쓰는 함수이며 강력한 함수인 vlookup인데 이 함수도 단점이 있습니다.

바로 테이블 순서가 바뀌면 찾지 못하는 상황이 발생합니다.

( vlookup을 모르시는 분은 아래의 글을 읽고 오시면 이해하는데 도움이 될 거 같습니다. )

※ vlookup의 한계

다음과 같이 테이블이 주어지면 정말로 무서울 것이 없는 함수인데

테이블 앞뒤가 바뀌었을 뿐인데 값을 가져오지 못하는 상황이 발생합니다.

( 혹시나 해서 -1을 입력했지만 결과는 오류를 반환합니다 )

---

이럴 때 xlookup 함수를 사용하면 vlookup의 한계 극복과 다양한 활용이 가능합니다.

우선 아래와 같이 데이터를 준비해 주었습니다.

---

기본 사용

=XLOOKUP(	E2,	$A$2:$A$21,	$C$2:$C$21   )
찾아줘	E2( "a" )의 값과	A2:A21에서 값이 있으면	C2:C21의 매출을

기존의 vlookup과 같이 테이블을 지정하는 것이 아닌 찾고자 하는 컬럼과 값을 찾고자 하는 컬럼을 지정해 줍니다.

vlookup과 동일하게 가장 위쪽에 있는 값을 반환해 주는 것을 볼 수 있습니다.

 

---

찾고자 하는 데이터가 없을 때 ( [if not found] )

기존 vlookup은 찾고자 하는 데이터가 없으면 일괄적으로 #N/A를 반화를 하였는데 xlookup은 데이터 없을 때 내가 원하는 단어로 지정이 가능합니다.

 

g라는 항목은 구분에 없기 때문에 #N/A를 반환하게 됩니다.

이 때 콤마를 하나 더 붙이고 데이터 없을 경우의 값을 넣어 주면 아래와 같이 "No Data"를 반환하게 됩니다.

---

조건에 맞는 다양한 값 찾기 ( [match mode] )

=XLOOKUP(	E7,	$C$2:$C$21,	$C$2:$C$21,	"No Data",	0   )
찾아줘	E7과 같은 값이	C2:C21매출 동일	C2:C21의 매출 값	없으면 이걸 반환	정확하게 일치

0	매출 300과 일치하는 데이터가 없기 때문에 No Data 반환
-1	매출 300의 바로 아래 값인 270을 반환합니다.
1	매출 300의 바로 다음 값인 349를 반환
2 ( 와일드 카드 )	문자 조건에 따라 값을 반환하는 ?는 한글자를 의미하므로 가장 위에 있는 값을 반환합니다.

반응형

※ 와일드카드 : 조건에 맞는 단어를 찾을 때 사용 ( ?, * 등이 사용 )

?	*
글자수	모든 글자
? ( 한글자 ), ?? ( 두 글자 ), ??? ( 세 글자 )	*주 (주로 끝나는 문자) 나* ( 나로 시작하는 문자 ) *리* ( 중간에 리가 있는 문자 )

천천히 잘 보시면 이유를 아실 수 있으실 겁니다.

---

원하는 방향에서 값 찾기 ( [search mode] )

 

마지막 옵션으로 오름차순이나 내림차순으로 값을 찾는 방향을 변경할 수 있습니다.

1을 입력시 오름차순 ( 가장 낮은 금액 )의 값을 찾고 -1을 입력 시 오름차순 ( 가장 높은 금액 )의 값을 반환하는 것을 볼 수 있습니다.