[엑셀 함수] HLOOKUP 함수 사용 방법

 

HLOOKUP은 VLOOKUP처럼 값을 찾아주는 함수입니다.

 

VLOOKUP이 찾고자 하는 값이 있는 행을 기준으로 특정 열의 값을 출력하는 함수라면,

HLOOKUP은 찾고자 하는 값이 있는 열을 기준으로 특정 행의 값을 출력하는 함수입니다.  

 

실무에서는 보통 데이터가 세로로 길기 때문에 VLOOKUP이 더 많이 활용되지만, 가로로 긴 표를 만들 때에는 HLOOKUP이 활용됩니다. 이번 글에서는 HLOOKUP의 사용방법과 예시를 소개드리겠습니다. 

 

예시로 쓰인 파일 출처는 공공데이터 포털입니다. 아래의 사이트에는 CSV 파일로 업로드되어 있지만 xlsx 파일로 변환해서 업로드했습니다. 

 

HLOOKUP_보건복지부_전국 지역보건의료기관 현황_20191231.xlsx

0.41MB

 

 

https://www.data.go.kr/tcs/dss/selectFileDataDetailView.do?publicDataPk=3072692 

보건복지부_전국 지역보건의료기관 현황_20191231

 

① HLOOKUP 함수 개념

HLOOKUP(기준값, 데이터 범위, 추출할 행의 번호, 일치옵션)

- 기준값: 데이터 범위의 첫 번째 행에서 찾고자 하는 값
- 데이터 범위: 데이터를 검색하고 추출하려는 범위
- 추출할 행의 번호: 추출하고자 행의 데이터 범위 기준 위치(행 번호)
- 일치 옵션: 유사일치 경우 TRUE(=1) / 정확하게 일치한 값 추출할 경우 FALSE(=0)    

HLOOKUP의 개념은 VLOOKUP과 굉장히 유사합니다. 다만, VLOOKUP이 가장 왼쪽 열을 기준으로 오른쪽의 데이터를 출력한다면, HLOOKUP은 가장 위의 행에서 아래쪽의 데이터를 출력합니다. 즉, 데이터를 찾는 방향이 다릅니다. 

위의 예시를 보면, 만약에 인천 내 건강생활지원센터 수를 추출하고자 한다면

1) 기준값에 "인천" 혹은 "인천"이 입력된 셀(D15)을 입력하고,
2) 전국 지역명 행(4행)부터 데이터 범위를 설정하고(기준값은 데이터 범위의 첫번째 행에 있어야 함),

3) 데이터범위 기준으로 '건강생활지원센터'는 두 번째 열이므로, 추출할 행의 번호에 2를 기입하고,

4) 일치옵션은 정확하게 일치한 값을 추출하도록 설정한 것입니다(FALSE 혹은 0 기입).
 

 

 ② HLOOKUP과 MATCH 함수 조합해서 활용하기

HLOOKUP(기준값, 데이터 범위, MATCH(추출할 행의 대푯값, 대푯값이 있는 열 범위, 0), 일치옵션)

- MATCH 함수: MATCH(찾을 값, 데이터 범위(줄 형태), 일치옵션)
- HLOOKUP의 '추출할 행의 번호' 부분: MATCH(추출할 행의 대푯값, 대푯값이 있는 열 범위, 0)
→ MATCH 함수는 찾을 값(추출할 행의 대푯값)의 데이터 범위(대푯값이 있는 열) 내 순번을 반환
→ 따라서, MATCH(추출할 열의 머리글, 대푯값이 있는 열 범위, 0)는 추출할 행의 번호를 출력함

HLOOKUP 함수를 활용하기 위해서는 결국 찾고자 하는 값이 몇 번째 행에 위치하는지 알아야 합니다. 따라서 행이 많은 경우 사용하기가 번거로울 수 있습니다. 그럴 때 추출할 행의 번호를 MATCH 함수를 통해 더 효율적으로 구할 수 있습니다. 뿐만 아니라, 드래그 또는 함수 복사/붙여넣기를 통해 여러 기준값의 복수의 데이터를 추출하는데 유용합니다. 

 

위의 예시를 보면, HLOOKUP 함수 내 출력할 행 번호 부분에 MATCH 함수가 들어가 있습니다.

MATCH($A16,$A$4:$A$11,0)를 살펴보면, '건강생활지원센터'(A16셀)가 보건기관명이 있는 열(A4:A11)에서 몇 번째 위치에 있는지 반환해줍니다. 건강생활지원센터는 A4:A11 범위(파란색 범위) 내에서 두 번째에 위치하므로, MATCH 함수는 2를 반환합니다. 그 결과 HLOOKUP 함수는 위의 사진처럼 서울 내 건강생활지원센터 개수(=0)를 출력합니다.

 

한편, 수식 HLOOKUP(B$15,$A$4:$S$11,MATCH($A16,$A$4:$A$11,0),FALSE)을 보시면 HLOOKUP과 MATCH 함수의 데이터 범위는 절대참조, 찾을 값은 혼합참조로 되어 있습니다. 그래서 수식을 다른 셀에 복사/붙여넣기 혹은 드래그하면 각 지역별 보건의료기관별 개수를 구할 수 있습니다. 

 

③ 언제 활용할까? 필요한 열의 데이터를 원하는 열 순서로 출력해야 하는 상황에서 유용

 

회사에서는 어떤 현황을 내부/외부에 보고할 때 특정 데이터만 요약해야 할 때가 많습니다.

데이터를 분석 요약할 때 가장 많이 쓰이는 것은 피벗테이블이지만, 문제는 예시 파일처럼 피벗테이블을 생성하면 기본적으로 모든 항목이 나타나고, 항목들이 오름차순으로 정렬되어 있습니다. 물론 필터링이나 사용자 지정 정렬로 필요한 데이터만 표시하고, 원하는 순서로 정렬할 수 있습니다. 하지만, 예시 파일처럼 HLOOKUP을 활용하여 별도의 표를 만드는 것이 시간이 더 절약됩니다.   

 

즉, 저는 HLOOKUP이 피벗테이블처럼 이미 요약된 자료에서 원하는 부분만 추출 및 편집할 때 활용하면 효율적인 함수라고 생각합니다. 

 

관련 글

[회사에서 많이 쓰이는 엑셀 함수] IF 함수 소개 및 사용법 #1
[파워쿼리] 파워쿼리로 다중조건 VLOOKUP 손쉽게 하기

VLOOKUP 함수의 여러 사용법과 용도 #1

[엑셀 기능] 표(테이블)과 피벗테이블

VLOOKUP 함수의 여러 사용법과 용도 #2