■ 목차
1. 함수 설명
2. Xlookup 함수의 구문 / 사용방법
3. Xlookup 함수 사용해 보기
4. Xlookup 함수로 여러 데이터 한 번에 찾아오기
5. Xlookup 함수 행방향으로 데이터 찾아오기
** 핵심 요약
1. 함수 설명
- Xlookup 함수는 기존의 Vlookup, Hlookup 함수의 업그레이드 버전이라고 보시면 됩니다.
- 다만 오피스 365 버전 등 최신 엑셀 버전에서만 사용이 가능합니다. ㅠㅠ
- 본 내용 또한 엑셀 365버전에서 작성이 되었습니다.
- 특정 값과 일치하는 값으로 원하는 데이터를 찾는 용도로 Vloolup함수를 많이들 사용 하였습니다.
- 그런데 이 Vlookup함수의 최대 단점이 바로 표에서 왼편에 존재하는 데이터를 찾을 수 없다는 것이었는데
- 이런 부분이 Xlookup 함수에서 개선이 되었습니다. 기존의 힘들게 Index, Match 함수로 찾던 부분이 이제는 Xlookup함수로 간단하게 찾는 것이 가능하게 되었습니다.
- 그 밖에도 몇 가지 개선된 기능이 있기는 하지만 가장 중요한 2가지는 금방 얘기드린 데이터를 ① 표의 왼편, 오른편 구분없이 참조되는 데이터를 찾을 수 있다는 것과
- 열방향 찾기(Vlookup 함수), 행방향 찾기 (Hlookup 함수)를 ② Xlookup함수 하나로 적용할 수 있다는 것이 가장 큰 것 같습니다.
- 그리고 오피스 365에서 특히 강조되었던 배열 형식(여러 항목의 범위로 여러 데이터 값을 찾아주는 기능)의 결괏값 반환도 눈에 띄네요.
2. Xlookup 함수의 구문 / 사용방법
- XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- find_text 검색할 텍스트
- lookup_array 검색할 배열 또는 범위
- return_array 반환할 배열 또는 범위
- [if_not_found] 선택인수 입니다. 유효한 일치 항목을 찾을 수 없는 경우 제공한 텍스트를 반환합니다.
- [match_mode] 일치 유형을 지정합니다. 0 혹은 생략 시 정확히 일치하는 데이터를 찾아 줍니다.
- [search_mode] 사용할 검색 모드를 지정합니다.
- 필수로 입력 할 인수가 3개이고 선택적 인수가 3개로 전체적인 인수는 기존보다 많아졌습니다.
- 하지만 거의 대부분은 필수 인수 3개만으로 기존과 동일한 작업을 할 수 있습니다.
- 일치 여부의 기본 옵션이(생략하는 경우) 정확하게 일치로 변경이 되었습니다.
- 기존에는 근사치를 찾아주는 옵션이 기본 옵션이여서 일치하는 값을 찾기 위해 항상 옵션을 지정해줘야만 했었습니다.
- find_text : 찾을(find) 문자(text)입니다.
- lookup_array : find_text를 찾고자(lookup)하는 범위(array)입니다.
- return_array : 반환할(return) 값이 있는 범위(array)입니다. 주의하실 점은 lookup_array와 return_array이가 같은 길이로 설정이 되어야 합니다.
- 원리가 lookup_array에서 일치하는 값을 찾아서 거기에 해당하는 위치의 값을 return_array에서 찾아주는 방식이기 때문입니다.
- lookup_array의 범위와 return_array의 범위의 길이가 일치하고 정확한 값을 찾기 위해서는 범위를 밀려서 선택하는 일이 없도록 해야 하겠습니다.
- return_array는 lookup_array의 위치와 무관하게 동일한 길이를 설정해 주시면 해당하는 위치의 값으로 찾기가 가능하고
- 행을 대상으로 하는 경우 길이만 같다면 여러 행을 한번에 선택해서 여러 개의 값을 가져오는 것도 가능합니다.
- [if_not_found] : 일치하는 값이 없는 경우 대체할 텍스트를 입력 할 수 있습니다. 입력된 값이 없으면 기존과 동일하게 #N/A 오류를 표시해 줍니다.
- [match_mode] : 일치 유형을 지정할 수 있는데 0 혹은 생략을 하게 되면 정확하게 일치+없으면 오류 반환(#N/A)입니다. 그냥 생략하고 사용하는 일이 많을 듯합니다.
- -1 (정확히 일치 + 없으면 다음으로 작은 항목 반환), 1 (정확히 일치 + 없으면 다음으로 큰 항목 반화), 2 (와일드카드 일치)
- [search_mode] : 1 혹은 생략하게되면 첫 번째 항목부터 검색 수행, -1은 마지막 항목부터 검색을 수행합니다.
- "find_text"의 문자를 "looup_array"에서 찾아서 거기에 해당하는 위치의 값을 "return_array"에서 찾아오는 함수라고 이해를 하시면 될 듯 합니다.
- 물론 열 방향, 행 방향 모두 사용이 가능합니다. 편리하게 사용이 가능할 듯 합니다.
3. Xlookup 함수 사용해 보기
- 다음은 인원별 하계 특강 신청현황입니다.
- 여기에서 신청자 성명으로 지역정보 및 신청과목 등을 찾아보도록 하겠습니다.
- 먼저 성명으로 지역정보를 찾아보겠습니다. 기존의 Vlookup으로는 성명 좌측 편에 있는 정보를 찾지 못했는데 Xlookup으로 간단하게 찾아보도록 하겠습니다.
- Xlookup 함수에서 먼저 첫 번째 인수 find_text은 검색할 값으로 "홍지민"(B3)입니다.
- 두 번째 인수인 lookup_array, 검색할 범위는 "성명"에 해당하는 "C6:C10"로 설정하고 여기에서 "홍지민"이라는 이름을 찾게 됩니다.
- 이렇게 "홍지민"이라는 이름을 찾으면 위에서 3번째에 해당하게 된다는 것을 알 수 있습니다.
- 마지막으로 세 번째 인수는 return_array로 반환할 값이 있는 범위로 "지역"열이 해당하는데 이때 주의 하실 점은 첫 번째 인수와 동일한 위치에서 동일한 길이로 지정을 해주셔야 합니다
- 세 번째 인수는 "B6:B10"으로 정확하게 lookup_array와 동일한 위치에서 범위를 지정해 줍니다.
- 이렇게 세 번째 인수를 지정하게 되면 이름을 찾을 때 우리가 확인한 위에서 3번째에 해당하는 "부산"이라는 지역 정보를 최종 가져오게 됩니다.
- "일치 유형"이랑 "검색 모드"는 따로 지정해주지 않았는데 기본 옵션으로 "정확하게 일치" 및 "위에서부터 검색"이 자동으로 적용이 됩니다.
- 우리가 가장 많이 사용하는 옵션이 기본옵션으로 선정이 되어 있어 편하게 사용을 할 수 있습니다.
4. Xlookup 함수로 여러 데이터 한 번에 찾아오기
- 다음은 "주민번호"와 "신청과목"을 한 번에 찾아보도록 하겠습니다.
- Xlookup 함수에서 여러 데이터를 한 번에 찾기 위해서 기존과 다르게 해줘야 하는 것은 return_array입니다.
- return_array 즉 "반환할 범위"를 한 열만( B6:B10 ) 지정하게 되면 한 개의 열에서 하나의 데이터를 찾아 주지만
- return_array를 여러 열( D6:E10 )을 지정하게 되면 여러 개의 데이터를 한 번에 찾을 수 있습니다.
- 이렇게 반환할 범위를 2개의 열로 지정하면 자동으로 배열형식으로 2개의 데이터를 모두 찾아서 결괏값으로 입력됩니다.
- Xlookup 함수는 Vlookup 함수의 복잡함으로 인해 받았던 많은 불평을 직관적이고 편하게 사용할 수 있도록 고민한 흔적이 보이는 듯합니다.
- 아주 편하게 사용을 할 수 있을 것 같습니다. 오피스 365를 구독해야 가능하지만. ^^
5. Xlookup 함수 행방향으로 데이터 찾아오기
- 이 번에는 행/열을 바꾸어서 행방향으로 데이터를 찾아보도록 하겠습니다.
- 여기서 행 방향으로 데이터를 찾는다는 의미는 "find_text"를 행방향으로 입력된 "lookup_array"에서 찾는다는 의미입니다.
- 과거 같으면 Hlookup 함수를 사용해서 찾는 방식입니다.
- 수식에서 보시는 것과 같이 Xlookup 함수는 행/열 방향을 가리지 않고 모두 사용이 가능합니다.
- 처음부터 이렇게 좀 만들어 주었으면 하는 바램도 있지만 어째던 지금부터는 잘 사용할 수 있을 듯합니다.
- 그냥 행방향으로 쭉 마우스로 드래그해서 대상 범위를 지정해 주는 것으로 적용이 가능합니다.
- 여기서도 lookup_array와 returrn_array의 길이가 같아야 하며 여러 행을 한 번에 return_array로 적용해서 여러 데이터를 한 번에 찾아오는 것도 동일하게 가능합니다.
** 핵심 요약 : Xlookup함수 사용법 - Vlookup, Hlookup 함수의 업그레이드 버전 (@엑셀 365)
1. 함수 설명
- Xlookup 함수는 엑셀 365에서 사용가능한 기존의 Vlookup, Hlookup 함수의 업그레이드 버전이다.
- 행방향, 열 방향 모두 적용 가능하며 기존에는 불가능했던 찾는 값의 좌측 데이터도 찾을 수 있다.
- 또한 여러 행/열을 지정해서 한 번에 여러 데이터를 배열형식으로 찾아오는 것도 가능하다.
2. Xlookup 함수의 구문 / 사용방법
- XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- find_text 검색할 값
- lookup_array 검색할 배열 또는 범위
- return_array 반환할 배열 또는 범위
- [if_not_found] 선택인수입니다. 유효한 일치 항목을 찾을 수 없는 경우 제공한 텍스트를 반환합니다.
- [match_mode] 일치 유형을 지정합니다. 0 혹은 생략 시 정확히 일치하는 데이터를 찾아 줍니다.
- [search_mode] 사용할 검색 모드를 지정합니다.
- 기본 옵션(생략 시 적용되는 옵션)이 "일치하는 값 찾기", "위에서 아래로 찾기"이기 때문에 필수 인수 3가지 만으로 정확하게 찾을 수 있다.
3. Xlookup 함수 사용해 보기
- 신청자 성명으로 특강 신청현황표에서 지역정보 등을 찾아보자
- find_text 인수로 "홍지민"을 입력하고 lookup_array, return_array는 같은 길이가 되도록 정확하게 입력하자.
- return_array는 lookup_array의 좌/우 상관없이 지정이 가능하게 되어있다.
4. Xlookup 함수로 여러 데이터 한 번에 찾아오기
- return_array, 반환할 범위를 한 열만 지정하면 한 개의 데이터를 찾아 주지만 여러 열을 지정하면 여러 개의 데이터를 한 번에 찾을 수 있다.
- 이때 return_array는 배열형식으로 한 번의 수식입력으로 여러 개의 데이터를 모두 찾게 된다.
5. Xlookup 함수 행방향으로 데이터 찾아오기
- Xlookup 함수는 행/열 방향을 가리지 않고 모두 사용이 가능하다.
- 그냥 행방향, 열 방향으로 마우스로 범위를 쭉 드래그해서 지정해 주면 된다.
- 단, lookup_array와 return_array의 방향과 길이는 같아야 한다.
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글