rand(life)
XLOOKUP함수(1) - 왼쪽으로 찾을 수 있다 본문
XLOOKUP함수는 엑셀 365버전에서만 쓸 수 있는 함수입니다. 일회성으로 구매하는 2019버전에서는 사용할 수 없습니다.
학교나 회사에서 365버전 라이선스를 취득하였는데도 아직도 구버전 엑셀을 사용하시는 분들이 많아서 이 글을 쓰게 되었습니다.
업무용으로 사용하는 함수 중에서 다섯번째안에는 드는 함수가 바로 VLOOKUP함수입니다. 매우 유용하지요. 하지만 이번에 새로 나온 XLOOKUP함수는 더 빨리지고 편해졌습니다. 그 내용을 정리해보면 8가지 정도가 됩니다만, 두 세번에 나누어서 포스팅하려 합니다
|
- 목차 (수정될 수 있음)- 2. 범위 가운데 열이 추가/삭제되어도 괜찮다 3. 입력할 인수가 적다. (따로 지정하지 않아도 정확히 일치하는 것을 찾는다.) 4. 아래방향부터 찾을 수도 있다 5. HLOOKUP함수도 대신할 수 있다 6. 결과값으로 범위를 반환시킬 수 있다 7. 에러메시지를 지정할 수 있다 8. 수식의 의미가 직관적이다 |
오늘은, 제가 생각하기에 XLOOKUP함수의 가장 큰 장점을 소개하려 합니다. 그것은, 제목에서 있다시피, "왼쪽으로 찾기가 가능하다"는 것입니다.
아시다시피, VLOOKUP함수는 주어진 값을 찾아, 그 값에서 "오른쪽으로" 일정 거리 떨어진 곳에 있는 값을 반환합니다.
이런 식으로 되어있는 표를 아주 흔하게 접하실겁니다. 여기서 I2셀에 학번만 입력하면 국어 점수를 나오게 하는 수식은 아래와 같습니다.
=VLOOKUP(I2,A2:G14,3,0)
이 수식의 의미는, I2셀에 있는 값(30301)을 A2:G14의 범위 중 "첫열"에서 찾아서, 그 위치에서 "오른쪽"으로 3칸 간 곳의 값을 가져오라는 의미입니다.
그런데, 아래와 같은 표라면 어떻게 해야할까요?
여기서는 찾고 싶은 국어점수는 학번의 "왼쪽"에 있기 때문에 VLOOKUP을 사용할 수 없습니다. VLOOKUP은 "오른쪽"에 있는 값만 찾을 수 있으니까요
이런 경우는 index, match함수를 섞어서 써야합니다. 위의 표에서 아래의 수식을 쓰면 가능합니다.
=INDEX($B$2:$B$14,MATCH(I2,$G$2:$G$14,0))
이전에 올린 글에도 설명이 있으니 참고하세요
그럼, 이제 새로 나온 XLOOKUP함수에서는 어떻게 가능할까요?
=XLOOKUP(I2,G2:G14,B2:B14)
이렇게 하면 됩니다. index, match함수를 섞어쓴 것보다 훨씬 간결해졌습니다. 위 수식을 말로 풀어쓰면 다음과 같습니다.
| 수식 | |
| XLOOKUP(I2, | i2셀에 있는 값을 |
| G2:G14, | G2:G14 범위에서 찾은 뒤, 그 값이 있는 상대적 위치와 같은 상대적 위치에 있는 값을 |
| B2:B14) | B2:B14 범위에서 찾아라 |
I2셀에 있는 값은 30301이었고,.
30301은 G2:G14범위에서는 첫번째 위치에 있습니다.
그러므로, B2:B14범위에서도 첫번째 위치에 있는 값인 93을 결과로 가져옵니다.
여기까지 설명을 들으면, 어떤 분들은 이렇게 생각하실지도 모릅니다.
첫번째 범위(G2:G14)와 두번째 범위(B2:B14)는 꼭 위의 예제처럼 붙어있지 않아도 되는거 아니야?
그렇습니다. VLOOKUP처럼 꼭 직사각형 형태의 범위에 있지 않아도 됩니다. 예를 들어, VLOOKUP함수에서 학번이 있는 범위와 점수가 있는 범위는 A2:G14 이런 식으로 네모 반듯한 직사각형, 또는 정사각형인 범위안에 들어있어야합니다. 즉, 학번이 있는 범위는 2행부터 14행까지, 점수가 있는 범위도 2행부터 14행까지로 시작행과 끝행이 일치해야한다는 것입니다.
하지만 XLOOKUP함수의 경우는, 두 범위가 서로 다른 행에서 시작해도 되고, 아예 다른 시트에 존재해도 됩니다.
위의 그림에서 보시다시피, 학번과 사회점수가 아예 따로 떨어진 곳에 있고, 시작행의 행번호가 다르더라도, 수식이 작동합니다.(학번은 2행부터 시작, 사회점수는 7행부터시작)
위의 그림처럼, 과학점수가 아예 현재 시트가 아닌 다른 시트에 존재하는 경우도 수식이 작동합니다.
하지만, 이렇게 편리하기때문에 틀리기 쉽고, 그래서 주의해야 할 점이 있습니다. 그것은 첫번째 범위와 두번째 범위의 크기가 일치해야한다는 것입니다.
위의 수식은 에러가 났습니다. 그 이유는, 수식의 첫번째 범위는 F2:F12로, 총 11개의 셀입니다만 두번째 범위는 L6:L17로, 총 12개의 셀입니다. 범위의 크기가 달라서 에러가 났습니다.
첫번째 범위는 제목행(학번,과목)을 제외시키고, 두번째 범위는 제목행을 포함시키거나, 그 반대의 경우로 범위를 지정하는 실수가 흔히 발생하리라 예상됩니다.
예제 파일을 첨부합니다. 당연하겠지만, 엑셀365버전이 없는 분은 제대로 작동하지 않습니다.
