학교에서만 배웠던 엑셀을 제대로 공부를 해봐야겠다고 마음 먹은 계기가 있다.
실무에서 엑셀을 자유자재로 사용하시던 부장님을 보고, "와~ 이거 노가다처럼 일하던거 간편하게 만들고, 그 시간에 더 유의미한 일을 할 수 있겠는걸?" 이라는 걸 느꼈다.
그리고 바로 국비지원이 되는 엑셀 강의를 등록했다. 지금도 너무 잘한 결정이라고 생각한다.
그 중 가장 많이 사용했던 lookup 함수를 먼저 소개한다.
(예제 파일도 첨부했으니, 필요한 분은 다운받으세요)
목차
1. xlookup 함수
2. vlookup 함수
3. hlookup 함수
4. 다중 조건 값 찾기
1. xlookup 함수
1) 특징
-lookup 함수 중 가장 최근에 생긴 함수 (엑셀 2021, M365 버전 부터 사용 가능)
-보통 오른쪽 아니면 왼쪽, 한 방향의 값만 가져올 수 있었지만, 이제는 방향 상관없이 가져올 수 있다.
2) 사용법
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP(기준값, 기준값이 있는 열 범위, 찾을 값이 있는 열 범위, 일치 항목이 없을때 반환할 텍스트, 일치 유형, 검색모드)
* [ ] 값은 선택 항목으로 기본 설정으로 진행시, 내용 쓰지않고 사용해도 됨.
*일치 유형을 생략시, 기본적으로 '정확한 일치 항목'을 생성함
예를 들어 [홍길동] 의 부서를 구한다고 했을때
*필수
-기준값 : 홍길동 (K3)
-기준값이 있는 열 범위 : 홍길동이 있는 열 (C2:C10)
-찾을 값이 있는 열 범위 : 부서가 있는 열 (A2:A10)
*나머지 생략 해도 일치 값으로 데이터 반환 됨.
만약, 회사 직원이 아닌 [OOO] 의 정보를 가져온다고 할 때, "일치 정보 없음" 으로 반환하고 있다면.
-일치 항목이 없을때 반환할 텍스트: "일치 정보 없음"
*텍스트를 넣을때는 앞,뒤 따옴표 꼭 사용.
=XLOOKUP(K3,C2:C10,A2:A10,"일치 정보 없음")
▼절대값 씌우면
=XLOOKUP($K3,$C$2:$C$10,A$2:A$10,"일치 정보 없음")
3) 선택사항 사용법
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP(기준값, 기준값이 있는 열 범위, 찾을 값이 있는 열 범위, 일치 항목이 없을때 반환할 텍스트, 일치 유형, 검색모드)
※ [match_mode] : 일치 유형
* 0 - 정확히 일치 (찾을 수 없는 경우 #N/A를 반환) <기본값
* -1 - 유사일치 (찾을 수 없는 경우 다음 작은 항목을 반환)
* 1 - 유사일치. (찾을 수 없는 경우 다음으로 큰 항목을 반환)
* 2 - 와일드카드 일치 (*, ?, ~ '와일드카드' 포함하여 유사일치)
와일드카드 일치 사용으로 다양한 상황에 대한 유사 값을 불러올 수 있다.
예를 들어 이름의 끝자리가 다른 '홍길O' 의 데이터를 찾을 수 있다.
※ [search_mode] : 검색 모드
*1 - 오름차순 (왼쪽 or 위쪽부터 검색) < 기본값
* -1 - 내림차순 (오른쪽 or 아래쪽부터 검색)
* 2 - 오름차순 이진 검색 (오름차순 정렬하지 않으면 잘못된 결과가 반환됨)
* -2 - 내림차순 이진 검색을 (내림차순 정렬하지 않으면 잘못된 결과가 반환됨)
위 경우는 일반적인 경우는 잘 쓸 일이 없다.
하지만 중복값이 있는 경우는 위에서 부터 값을 불러올지, 아래에서 부터 불러올지 선택할 수 있다.
2. vlookup 함수
1) 특징
-예전 버전의 엑셀을 사용하고 있다면, 이 함수를 많이 쓰고 있을거다.
-단점은 기준점에서 오른쪽 방향의 데이터만 값을 찾아올 수 있다.
*기준 점이 맨 왼쪽에 있어야 함
예) 홍길동 에 대한 데이터를 찾아온다고 했을때, 홍길동의 오른쪽인 사번과 전화번호는 찾을 수 있지만, 왼편인 부서와 직급은 가져올 수 없다.
(이런 경우, 왼편의 자료를 오른편으로 이동, 또는 추가해서 사용할 수 있다.)
2) 사용법
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(기준값, 자료범위, 찾을 값의 열 번호, 대략 or 일치반환)
* [ ] 값은 선택 항목
*하지만 vlookup은 기본 설정이 유사일치(대략적반환) 이므로, '일치반환' 꼭 같이 설정해야 함
예를 들어 [홍길동] 의 사번&전화번호를 구한다고 했을때,
-기준값 : 홍길동 (G3)
-범위 : 홍길동~전화번호 (C2:E10)
-열 번호 :
> 사번의 경우, 설정한 범위(C2:E10) 중 2번째 열에 해당하므로 (2)
> 전화번호의 경우, 설정한 범위(C2:E10) 중 3번째 열에 해당하므로 (3)
-대략 or 일치반환 :
> 대략적 반환은, 1 또는 TRUE
> 정확히 일치 반환은, 0 또는 FALSE
[사번]
=VLOOKUP(G3,C2:E10,2,FALSE)
[전화번호]
=VLOOKUP(G3,C2:E10,3,FALSE)
▼절대값 씌우면
=VLOOKUP($G3,$C$2:$E$10,2,FALSE)
=VLOOKUP($G3,$C$2:$E$10,3,FALSE)
3. hlookup 함수
1) 특징
-vlookup 의 가로 버전
2) 사용법
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
=HLOOKUP(기준값, 자료범위, 찾을 값의 행 번호, 대략 or 일치반환)
예를 들어 [홍길동] 의 부서&직급를 구한다고 했을때,
-기준값 : 홍길동 (H13)
-범위 : 홍길동~전화번호 (A13:E15)
-열 번호 :
> 부서의 경우, 설정한 범위(A13:E15) 중 2번째 행에 해당하므로 (2)
> 직급의 경우, 설정한 범위(A13:E15) 중 3번째 행에 해당하므로 (3)
-대략 or 일치반환 :
> 대략적 반환은, 1 또는 TRUE
> 정확히 일치 반환은, 0 또는 FALSE
[부서]
=HLOOKUP(H13,A13:E15,2,FALSE)
[직급]
=HLOOKUP(H13,A13:E15,3,FALSE)
▼절대값 씌우면
[부서]
=HLOOKUP(H$13,$A$13:$E$15,2,FALSE)
[직급]
=HLOOKUP(H$13,$A$13:$E$15,3,FALSE)
4. 다중조건 값 찾기
1) vlookup 으로 다중 조건 값 찾을 경우 (xlookup 으로도 가능)
-새로운 기준을 찾을 값 보다 좌측에 열을 추가 해야 한다.
예) 영업팀이면서 부장인 사람의 '이름/사번/전화번호' 를 찾는다고 한다면.
'이름(D열)' 왼편(C열) 에 조건1과 조건2를 합친 데이터를 넣는다.
C21
=A21&B21
그리고 값을 찾을 J열에는 기준값을 H21&I21 로 적는다.
J21
=VLOOKUP($H21&$I21,$C$20:$F$25,2,FALSE)
2) INDEX / MATCH 활용한 방법
-차라리 다중 검색은 이 방법이 더 편하고, 자주 쓰인다.
그런데 이건 또 두 함수를 알아야 해서 따로 다룰 예정...