어떤 표에서 내가 찾고자 하는, 원하는 위치에 있는 데이터를 불러오는 함수가 바로, VLOOKUP / HLOOKUP 이 있었습니다.
※ V/HLOOKUP 함수의 자세한 사용법은 이전 글을 참고해주세요!
내가 찾으려는 데이터가 있긴 한데, VLOOKUP으로도, HLOOKUP으로도 원하는 걸 얻지 못할 때가 있어요.
예시를 한번 볼게요.
위처럼 데이터가 중복되는 경우죠.
내가 원하는 데이터는 2번째의 이다정 학생의 출석 점수만을 원해요.(억지 같지만 실제로 빈번하게 있는 경우랍니다!)
학생 이름이 중복되지만 않았더라면 VLOOKUP함수로 아주 간단하게 출석 점수를 얻을 수가 있죠.
이렇게 말이죠!
13행에 있는 이다정 학생의 이름을 잠시 바꿔서 수식계산을 해보았어요.
사용한 함수를 한번 말로써 해석해볼까요?
이다정이라는 단어를 A3:G18의 범위에서 찾은 다음, 6번째 열에 있는 내용을 반환하세요.
하지만 여기서 VLOOKUP 함수가 가진 한계가 있어요. 바로 "이다정"이라는 단어가 2개, 3개, 여러 개가 있을 때에도 무조건 첫 번째 찾은 것만 가져온다는 거예요.
다시 데이터를 원래로 되돌려놓고 계산을 해볼게요.
수식은 그대로인데 결괏값이 바뀌어버렸어요.
예시의 테이블은 간단하기 때문에 잘못되었다는 것을 알아차릴 수 있지만, 우리가 실무에서 사용하는 엑셀표는 너무나도 많은 데이터를 담고 있기 때문에 틀렸다는 사실을 알아차리지 못하고, 또 그로 인해 많은 수식들이 어긋나는 등의 치명적인 실수가 될 수 있어요.
이럴 때는 어떻게 해결해야 할까요? IF문을 이용하여, "이다정" 단어가 2개일 때 더 아래쪽에 있는 행을 찾아서 6번째 열의 점수를 가져오고...........
생각만 해도 수식이 복잡해질 것 같네요.
이런 문제를 해결하기 위하여 새로 추가된 함수입니다.
너무나 소중한 XLOOKUP 함수입니다.
V&HLookup의 인수는 3개인 데에 비해 XLOOKUP의 인수는 무려 6가지나 됩니다.
인수를 넣어봅니다.
함수 인수의 이름들에 조금 익숙해질 필요가 있습니다.
=XLOOKUP("이다정",A3:A18,F3:F18)
"이다정"이라는 값을 A3:A18 범위 내에서 찾아서 F3:F18 범위 내에 같은 위치에 있는 값은 반환하세요.
여기까지는 위의 VLOOKUP 함수와 비슷합니다.
다만 검색범위와 반환하는 방법이 다를 뿐이네요. 그런데 XLOOKUP함수는 선택 인수, 그러니까 써도 되고 안 써도 되는 [ ] 대괄호 안의 인수가 3개가 추가로 있습니다.
| 함수 인수 | 설명 | 특징 | |
| Lookup_value | 검색할 값 | 필수(그러나 생략하면 빈 셀을 찾음) | |
| Lookup_array | 검색할 배열 또는 범위 | 필수 | |
| Return_array | 반환할 배열 또는 범위 | 필수 | |
| [If_not_found] | 유효한 일치를 찾을 수 없는 경우에 나타내는 텍스트 | 선택 (공백으로 둘 경우 #N/A가 표시) |
|
| [Match_mode] | 일치 유형을 지정 | 0 : 정확한 일치, 없는 경우 #N/A | 선택 (기본값은 0) |
| -1 : 정확한 일치, 없는 경우 그 다음 작은 항목 | |||
| 1 : 정확한 일치, 없는 경우 다음 큰 항목 | |||
| 2 : 와일드카드 일치* | |||
| [Search_mode] | 검색모드 | 1 : 첫번째 항목부터 검색 | 선택 (기본값은 1) |
| -1 : 마지막 항목부터 역방향 검색 | |||
| 2 : 오차도 순서로 정렬되는 lookup_array 이진 검색 | |||
| -2 : 내선 순서로 정렬되는 lookup_array 이진 검색 | |||
V/HLOOKUP 에서 아쉬웠던 부분을 채워줄 Optional 인수들입니다.
이 숨은 인수들도 해석을 해볼게요.
=XLOOKUP("이다정",A3:A18,F3:F18)
"이다정"이라는 값을 A3:A18 범위 내에서 찾아서 F3:F18 범위 내에 같은 위치에 있는 값은 반환하세요.
[만약에 값을 못 찾았을 땐 #N/A로 표시하고], [완전히 똑같은 값만 검색하고], [표의 첫번째 항목부터 검색하세요.]
수식은 바뀌지 않았지만 사실은 저런 명령들이 숨어있었던 거예요.
대체로 이렇게 사용을 많이 하니까 기본값으로 둔 것인데, 또 많은 경우에 다른 방법으로도 사용을 하게 되니 쓰는 사람이 필요할 때 쓰라고 사용자에게 선택의 권한을 준 것이죠.
자, 다시 데이터를 볼게요.
우리는 어쨌든 2번째 "이다정"의 데이터가 필요하고, VLOOKUP과 기본 XLOOKUP 함수는 위 → 아래로 검색하는 것이 기본이니, 우리는 아래 → 위로, 역방향 검색을 하면 원하는 데이터를 찾을 수 있겠죠?
[If_not_found]도 선택 값이라 굳이 쓰지 않아도 되지만, 찾는 값이 없을 때 셀에 #N/A라고 뜨는 게 저는 왠지 거슬리더라고요. (개인 의견)
그래서 [만약 찾는 값이 없을 때는 "해당 없음"이라고 표시해라]는 명령을 4번째 인수에 넣었고
5번째 인수 [match_mode]는 어차피 정확하게 일치하는 데이터를 찾을 것이니 공백으로 두었어요.
쉼표가 2개가 있는 것처럼 보이지만 사실은 인수와 인수 사이에 공백이 있는 것이랍니다.
"이다정"이라는 값을 A3:A18 범위 내에서 찾아서 F3:F18 범위 내에 같은 위치에 있는 값은 반환하세요. 만약에 값을 못 찾았을 땐 "해당없음"로 표시하고, [완전히 똑같은 값만 검색하고], 표의 마지막 항목부터 검색하세요.
위와 같은 의미의 수식이 만들어진 것입니다.
이 XLOOKUP 함수는 엑셀 프로그램이 업데이트되면서 새로 생긴 함수입니다.
지금까지는 위와 같은 선택 인수가 없어서 원하는 값을 추출하려면 너무 많은 함수들이 중첩되어야만 했어요.
V/Hlookup 뿐만 아니라 Index-match 같은 값을 검색하는 함수들의 고질적인 문제점은 무조건 1번 행부터, 위에서부터 검색한다는 것이 문제였습니다.
이 지독한 단점을 깨끗하게 해결해준 Xlookup입니다.
그래서 저는 Vlookup과 Xlookup을 구분해서 쓰지 않고, Xlookup을 더 자주 사용하게 되었습니다. 이 두 함수는 Vlookup ⊂ Xlookup 부분집합의 관계니까요!
그럼, 혹시 같은 값이 3개일 때는 어떻게 검색을 해야 할까요...?
우리가 해결해야 할 숙제네요ㅎㅎ
XLOOKUP 함수, 많이 활용해보시고 궁금한 점 있으면 댓글 달아주시기 바랍니다.
'엑셀 > 엑셀 함수' 카테고리의 다른 글
| [엑셀함수] 기념일 날짜, 납기일 구하는 함수 : DAYS (+today함수) (0) | 2022.04.06 |
|---|---|
| [엑셀함수] INDEX-MATCH : 표에서 원하는 값 찾기 함수 (0) | 2022.03.27 |
| [엑셀함수] 순위를 매기는 RANK함수 (0) | 2017.04.13 |
| [엑셀함수] 필터값만 계산하고 싶을 때 사용하는 SUBTOTAL함수 (5) | 2017.04.09 |
| [엑셀함수] 셀의 개수를 세는 COUNT함수(COUNTA) (0) | 2017.03.23 |