■ 목차
1. 함수 설명
2. Hlookup 함수의 구문 / 사용방법
3. Hlookup 함수 사용해 보기 -사원의 입사일과 소속을 찾아보자
4. Hlookup 함수 주의 사항
5. Hlookup 함수 - 다중 조건 찾기
** 핵심 요약
- Hlookup 함수는 Vlookup 함수와 동일한 기능으로 표나 테이블 같은 곳에서 원하는 값을 찾아오는 함수입니다.
- Vlookup과 동일한 방식으로 사용을 하지만 값을 찾는 방향이 H(Holizontal, 수평 방향)입니다.
- 다중 조건의 경우 데이터를 & 연산자로 간단하게 결합하는 방법 등으로 사용을 할 수 있습니다.
- 무엇보다 데이터 형식이 우리가 평소에 정리하는 세로방향이 아니라서 우선 표나 테이블을 잘 정리하고 있는지를 먼저 고밀해 보시고 사용하시는 것이 좋을 듯합니다.
1. 함수 설명
- Hlookup 함수는 "H(Holizontal, 수평 방향)" + "lookup (데이터 찾기)"으로 Vlookup, Xlookup 함수와 동일한 기능의
- 일치하는 데이터를 찾아서 해당하는 값을 찾아오는 함수라고 보시면 됩니다.
- 여기서 Holizontal은 영어로 수평, 수평선이라는 뜻으로 아래의 표와 같이 가로방향으로 데이터들이 나열된 형식을 의미합니다.
- 그래서 Hlookup 함수는 지정된 값을 가로방향(Holizontal)으로 찾아 주는 함수입니다.
- Vlookup 함수와 동일한 사용방법이고 데이터를 찾는 방향만 다른 함수이기 때문에 원리만 이해를 하시면 둘 다 쉽게 사용이 가능한 함수입니다.
- 1) Hlookup의 데이터 형식 및 찾기 방향
- Hlookup 함수의 찾기 데이터 형식은 아래와 같이 가로방향 (= 수평방향, Holizontal)으로 작성된 표 형식입니다.
- 2) Hlookup의 데이터를 찾는 원리 = 우리가 데이터를 찾는 방식
- 실제로 우리가 데이터를 찾아가는 방식을 이해하고 Hlookup 함수가 어떻게 데이터를 찾아내는지는 알아보도록 하겠습니다.
- 일단 ① 우리는 "윤서현" 사원이라는 이름으로 데이터를 찾고자 합니다.
- 해당 데이터를 찾기를 할 ② "사원 정보"의 원본 데이터로 표와 같은 테이블이 존재해야겠죠
- ③ 첫 번째 줄의 "사원명"에서 데이터의 방향 (=수평방향, 가로방향)으로 "윤서현"이라는 사원을 찾습니다.
- ④ 마지막으로 "윤서현"사원의 "입사일"을 4번째 줄에서 확인을 하는 순서입니다.
- Hlookup이라는 함수도 방금 우리가 데이터를 찾는 것과 동일한 방식으로 데이터를 찾고 동일한 인수를 입력해 주시면 됩니다.^^
2. Hlookup 함수의 구문 / 사용방법
- 아래 구문은 엑셀 도움말의 설명 내용입니다. 우리는 엑셀 함수의 구문을 다 외울 수도 없고 그럴 필요도 없습니다.
- 다만 엑셀 도움말과 함수사용 시 표시되는 풍선도움말의 용어들에 익숙해질 필요가 있기 때문에 기회가 될 때마다 편하게 봐주시기 바랍니다.
- HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value 필수 요소입니다. 표의 첫 행에서 찾을 값입니다. lookup_value는 값, 참조 또는 텍스트 문자열일 수 있습니다.
- table_array 필수 요소입니다. 데이터를 찾을 정보 표입니다. 범위에 대한 참조나 범위 이름을 사용합니다.
- row_index_num 필수 요소입니다. 반환하려는 값이 있는 table_array의 행 번호입니다.
- range_lookup 선택 사항입니다. HLOOKUP에서 정확한 일치 또는 대략적인 일치를 찾을지 여부를 지정하는 논리적 값입니다.
- 첫 번째 인수는 lookup_value입니다. 찾고자 하는(lookup) 값(value)이라고 보시면 됩니다.
- 보통은 찾고자 하는 값이 입력되어 있는 셀 주소를 지정해 주시만 직접 입력하는 텍스트의 경우 반드시 텍스트임을 표시하기 위해서 큰따옴표( " " )를 사용해 주셔야 합니다. ( "윤서현" )
- 두 번째 인수는 table_array로 전체 테이블 범위입니다. 여기서 주의를 해주실 부분은 반드시 첫 번째 가로 줄(행)은 lookup_value가 존재하는 행이 되어야 합니다.
- Hlookup 함수의 일종의 규칙이자 단점 같은 것인데 바로 첫 번째 행(row)에서 데이터를 찾기 때문입니다.
- 세 번째 인수 row_index_num로 찾고자 하는 행(row) 번호(index_number), 여기서 첫 번째 행(row)이 숫자 1이 되고 순서대로 2, 3,.. 이 되게 됩니다.
- 그래서 우리가 찾고자 하는 "입사일" 같은 경우는 4번째 행(row)이 되게 됩니다.
- 여기서 행번호라면 우리가 지정한 전체 표에서의 행 번호 이기 때문에 찾기 목적상 표의 일부만을 사용하는 경우에는 지정한 표에서 행번호를 입력해 주시면 됩니다.
- 네 번째 인수 range_lookup은 True = 1 혹은 False = 0을 입력할 수 있는데 보통은 "정확하게 일치"하는 값을 찾기를 하는 경우가 많기 때문에 생략하지 않고 "False 혹은 0"을 입력하게 됩니다.
- 생략하거나, True 혹은 1을 입력하게 되면 비슷한 값을 찾을 수 있는데 이 경우 lookup_value에 해당하는 데이터가 사전에 정렬이 되어 있어야 제대로 찾기를 해주고 그렇게 많이 사용하는 옵션은 아닙니다.
- 인수를 순서대로 해석을 해보면 "윤서현(lookup_value)을 사원정보 테이블(table_array)의 첫 번째 행(row)에서 찾아서 4번째 행(row_index_num)의 입사일을 정확하게(False) 일치하는 옵션으로 찾아줘!" 정도가 됩니다.
- Hlookup("윤서현", "사원 정보 테이블", 4번째 행, 정확하게 일치 옵션) = Vlookup("윤서현", B2:G6, 4, Fasle)가 됩니다.
- 그리고 참조로 Vlookup 함수는 찾기를 하는 방향만 다르고 동일하게 사용을 하시면 됩니다. ^^
3. Hlookup 함수 사용해 보기 -사원의 입사일과 소속을 찾아보자
- Hlookup 함수로 "사원 정보" 데이블에서 "소속"과 "성별"을 구해보도록 하겠습니다.
- Hlookup, Vlookup 함수로 원하는 정보를 찾아올 수 있는데, 가장 먼저 해야 하는 일은 어떤 함수를 사용해서 정보를 찾아 올 것인가를 결정하는 일입니다.
- 표와 같이 사원명이 가로방향( = 수평 방향, Holizontal)으로 데이터가 구성된 경우는 Hlookup 함수를 사용해줘야 하고
- 찾고자 하는 값("강수빈")의 데이터가 첫 번째 줄에 있는지를 확인해줘야 합니다.
- 다시 얘기를 드리지만 Hlookup 함수는 표의 1번째 줄에서 찾아서 1, 2, 3, …. 번째 줄의 같은 위치의 값을 가져오는 함수입니다.
- 1) 소속 찾기
- "강수빈" 사원의 "소속"은 Hlookup 함수로 아래와 같이 찾아올 수 있습니다.
- 적용 수식 : =HLOOKUP( B8, $B$2:$G$5, 2, FALSE )
- ① 첫 번째 인수(lookup_value)는 찾고자 하는 값이고 "=hlookup("까지 키보드로 입력을 한 후 찾고자 하는 "강수빈"의 값이 입력되어 있는 "B8" 셀 주소는 마우스나 키보드의 화살표로 선택하여 셀 주소를 입력합니다.
- 그리고 인수 구분자인 "쉼표( , )"를 키보드로 입력합니다.
- "=hlookup(B8, "까지 입력이 되었습니다.
- ② 두 번째 인수는 table_array로 "사원 정보" 표의 전체 테이블입니다.
- 여기서는 찾고자 하는 "강수빈"이 1번째 행(row)이 되어야 하고 최종적으로 가져오고자 하는 "소속"열까지 반드시 포함이 되는 전체 범위를 선택해주셔야 합니다.
- 그리고 보통 수식 복사를 하는 경우 많기 때문에 table_array 범위의 셀 주소를 "절대 참조"형식으로 F4 단축키를 사용해서 입력해 줍니다.
- "B2:G5"까지의 전체 범위를 마우스 드래그로 선택을 해주시고 바로 "F4"키를 눌러 주시면 "$B$2:$G$5"와 같이 절대 참조 형식의 주소로 바뀌게 됩니다.
- 그리고 인수 구분자 쉼표( , )까지 입력하면 "=hlookup(B8,$B$2:$G$5, "까지 입력이 되게 됩니다.
- ③ 세 번째 인수는 "row_index_num"로 최종적으로 가져오고자 하는 "소속"에 해당하는 열 번호를 숫자로 적어 주시면 되는데
- 항상 두 번째 인수로 선택한 표 전체에서 1, 2, 3,.. 순서로 번호를 인식하기 때문에 "소속"은 숫자 2가 되게 됩니다.
- 인수 구분자까지 입력하면 "=hlookup(B8,$B$2:$G$5, 2, " 수식까지 작성이 되었습니다.
- ④ 마지막 인수는 옵션으로 "False 혹은 숫자 0"을 입력하여 "강수빈"과 정확하게 일치하는 값을 찾을 수 있도록 지정합니다.
- 완성된 수식 : =HLOOKUP ( B8, $B$2:$G$5, 2, FALSE )
- 2) 성별 찾기
- "강수빈" 사원의 "성별"은 Hlookup 함수로 아래와 같이 찾아올 수 있습니다.
- 적용 수식 : =HLOOKUP ( B8, $B$2:$G$5, 3, FALSE )
- 동일하게 "강수빈" 사원의 정보 중에서 이번에는 "성별 (=3 번째 행)"을 찾아오는 수식입니다.
- 동일한 수식을 사용하시고 다만 row_index_num 인수만 3으로 3번째 "성별"을 가져올 수 있도록 지정해 주시면 됩니다.
3) 수식 복사하기
- 다음으로 "신민성" 사원의 "소속"과 "성별"은 복사/붙여 넣기를 해주거나 "자동 채우기"를 해서 수식복사를 해주시면 됩니다.
4. Hlookup 함수 주의 사항
- Hlookup 함수, Vlookup 함수 처음에는 이것저것 지정하는 값들이 많아서 어려워 보이지만
- 실제로는 우리가 데이터를 찾아가는 방식과 동일하고 추가 옵션으로 정확하게 일치하는 값을 찾을 건지 비슷한 값을 찾을 것인지를 지정할 수 있도록 기능이 확장되어 있는 정도입니다.
- 아래 몇 가지 주의사항을 참조하시고 사용하시면 좋을 듯합니다.
- 1) Vlookup 함수 : 사용 방법이 동일하고 찾기를 하는 방향만 다르다 (세로, 열 방향 = Vlookup 함수, 가로, 행 방향 = Hlookup 함수)
- 사실 Hlookup 함수는 그렇게 많이 사용할 일이 없습니다. 우리가 Hlookup 함수를 사용할 수 있는 형식으로 테이블을 잘 작성하지 않기 때문입니다.
- Hlookup 함수의 사용이 필요하다면 데이터 작성 방향을 다시 한번 고민해 보시는 것을 추천드립니다.
- 2) 항상 표의 첫 행(Row)에서 찾는다.
- lookup_value 인수인 "강수빈"은 항상 지정된 테이블 범위에서 첫 번째 행에서 찾기를 합니다.
- 그래서 전체 테이블 범위를 지정할 때 항상 lookup_value가 첫 번째 행이 되도록 범위를 지정해 주거나
- 아니면 강제로 행의 순서를 바꾸어 주어야 합니다.
- Hlookup 함수를 엑셀에서 그렇게 프로그램해서 만들었기 때문에 반드시 지켜야 하는 규칙입니다.
- 이런 제한 때문에 Xlookup함수나 Index, Match 함수를 대신 사용하곤 합니다.
- 3) 추출하고자 하는 값의 행은 항상 아래에 존재해야 한다.
- 1 번째 행에서 찾기를 해고 첫 번째 행을 포함하여 아래에 있는 행에서만 최종 값을 가져올 수 있습니다.
- 1번째 행이 숫자 1이고 아래로 2, 3, 4, … 와 같이 row_index_num가 부여되고 지정이 가능합니다.
- 그래서 숫자 0이나 음수를 row_index_num의 인수로 입력할 수 없고 입력 시 오류가 나게 됩니다.
- 4) 옵션은 거의 "일치하는 단어 찾기"이고 "False 혹은 숫자 0"을 입력해줘야 한다.
- 생략가능한 기본 옵션은 "True"로 유사한 값을 찾아 주게 됩니다.
- 하지만 우리는 거의 "일치하는 단어 찾기"을 가장 많이 사용하고 거의 항상 숫자 0이나 "False"을 입력해줘야 합니다.
- 이게 엑셀 365등 최신 버전에서는 Xlookup 함수로 업그레이드되면서 기본 옵션도 "일치하는 값"찾기로 좋아졌습니다.
- 5) 대표 오류 2가지 : 전체 테이블은 절대참조 형식으로 할 것, 테이터 형식/공백으로 못 찾는 경우가 많다.
- Hlookup 함수를 사용하면서 가장 많이 발생하는 오류 2가지입니다.
- ① Hlookup 함수를 적용하고 수식을 복사하는 경우 전체 테이블을 지정하는 table_array 인수는 거의 100% 절대 참조 형식으로 사용을 해야 합니다.
- 복사를 하는 경우 수식 구조상 항상 같은 전체 테이블 영역을 참조해야 하기 때문입니다. 수식을 복사하는 순간 상대참조의 테이블주소는 오류를 만들게 됩니다.
- ② 분명히 찾는 값이 존재하는데 못 찾아오는 경우입니다. 못 찾아온다는 것이 기준은 컴퓨터 기준으로 불일치하기 때문입니다.
- 가장 대표적인 것이 보이지 않는 공백이나 데이터 형식이 달라서 날짜 2/1과 텍스트 2/1이 다르게 인식되는 사례와 같은 것들입니다.
- 필요하면 사전에 데이터 형식이나 공백 등을 제거하는 사전 작업을 한 후에 사용해 주시기 바랍니다.
- 6) 찾고자 하는 값이 2개 이상인 경우 첫 번째만 찾아 준다.
- Hlookup 함수는 순서대로 좌측에서 우측으로 첫 번째 행(Row)에서 찾기를 합니다.
- 그리고 첫 번째 값만을 찾기 할 수 있고 2번째, 3번째 찾기를 하고자 한다면 조금 특수하게 배열 수식을 사용해줘야 합니다.
- 만약 MS 365 등 최신버전의 엑셀을 사용한다면 Xlookup 함수는 찾고자 하는 값을 지정하여 2번째 3번째 값을 찾는 것이 가능합니다.
5. Hlookup 함수 - 다중 조건 찾기
- 일단 Hlookup 함수의 일반적인 기능으로는 찾기가 좀 힘이 듭니다. 몇 가지 꼼수로 찾을 수 있는데
- 1) 2개의 조건에 해당하는 텍시트를 결합(&연산자)하는 행을 추가해서 사용하는 방법
- 2) 여러 조건으로 검색 = 필터 기능 고유 기능으로 필터를 적용하여 검색하는 방법
- 3) Index, Match 함수를 배열 수식으로 적용하는 방법
- 1) 2개의 조건에 해당하는 텍시트를 결합(&연산자)하는 행을 추가해서 사용하는 방법
- 가장 전통적이고 심플한 방법입니다. 아래와 같이 찾고자 하는 2개의 조건을 연결하는 새로운 행을 추가해서
- 추가된 행을 기준으로 Hlookup 함수를 적용하시면 됩니다.
- 2) 여러 조건으로 검색 = 필터 기능 고유 기능으로 필터를 적용하여 검색하는 방법
- 사실 Hlookup 함수 대신으로 사용하기에는 조금 무리가 있는 방법입니다.
- 필터 기능을 사용하게 되면 여러 조건에 일치하는 데이터를 필터링해서 값을 찾을 수는 있지만
- Hlookup 함수를 사용해야 하는 경우의 데이터 형식으로 가로로 데이터가 나열된 형식이라 필터기능을 사용할 수가 없습니다.
- 그래서 먼저 데이터의 정렬된 방향부터 변경이 필요하고 그 후에야 필터 기능 사용이 가능하기 때문입니다.
- 데이터 나열 형식 변경 : (복사) > (마우스 우클릭) > 선택하여 붙여 넣기 > "행/열 바꿈"
- 3) Index, Match 함수를 배열 수식으로 적용하는 방법
- 초보분들은 그냥 참조를 하셔도 됩니다. ^^
- 일치하는 값의 위치를 찾아주는 Match 함수랑 지정한 위치의 값을 가져오는 Index 함수를 배열 형식으로 사용해서 다중 조건의 값을 찾아오는 배열 수식입니다.
- 오늘은 이런 방식도 가능하다 정도로 이해해 주시고 Index, Match 함수 편에서 자세한 내용을 참조하시기 바랍니다.
** 핵심 요약 : Hlookup 함수 사용법 - Vlookup 함수와의 차이, 다중 조건 찾기
1. 함수 설명
- Hlookup 함수는 "H(Holizontal, 수평 방향)" + "lookup (데이터 찾기)"으로 표나 테이블 같은 곳에서 원하는 값을 찾아오는 함수이다.
- Vlookup과 동일한 방식으로 사용을 하지만 값을 찾는 방향이 H(Holizontal, 수평 방향)이다.
- 실제로 우리가 표 같은 곳에서 데이터를 찾아가는 방식과 동일하게 사용을 하면 된다.
2. Hlookup 함수의 구문 / 사용방법
- HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value 필수 요소입니다. 표의 첫 행에서 찾을 값입니다. lookup_value는 값, 참조 또는 텍스트 문자열일 수 있습니다.
- table_array 필수 요소입니다. 데이터를 찾을 정보 표입니다. 범위에 대한 참조나 범위 이름을 사용합니다.
- row_index_num 필수 요소입니다. 반환하려는 값이 있는 table_array의 행 번호입니다.
- range_lookup 선택 사항입니다. HLOOKUP에서 정확한 일치 또는 대략적인 일치를 찾을지 여부를 지정하는 논리적 값입니다.
- 첫 번째 인수는 lookup_value로 찾고자 하는(lookup) 값(value)라고 보면 된다.
- 두 번째 인수는 table_array로 전체 테이블 범위이다. 반드시 첫 번째 행은 lookup_value가 존재하는 행이 되어야 한다.
- 세 번째 인수 row_index_num로 찾고자 하는 행(row) 번호(index_number)이다. 지정한 전체 테이블에서 첫 번째 행이 1이 되고 차례대로 2, 3, …이 되는 방식이다.
- 네 번째 인수 range_lookup은 옵션으로 정확하게 일치하는 데이터를 찾기 위해서 False 혹은 숫자 0을 입력한다.
3. Hlookup 함수 사용해 보기 -사원의 입사일과 소속을 찾아보자
- Hlookup 함수로 "사원 정보" 데이블에서 "소속"과 "성별"을 구해보자.
- 1) 소속 찾기, 적용 수식 : =HLOOKUP ( B8, $B$2:$G$5, 2, FALSE )
- ① 첫 번째 인수(lookup_value)는 찾고자 하는 값으로 "강수빈"의 해당하는 셀 주소를 마우스로 선택해서 입력한다.
- ② 두 번째 인수는 table_array로 "사원 정보" 표의 전체 테이블이다. 수식을 복사해서 사용하기 위해 절대 참조 형식으로 입력한다. (F4키 사용)
- ③ 세 번째 인수는 "row_index_num"로 최종적으로 가져오고자 하는 "소속"에 해당하는 열 번호 2를 입력한다.
- ④ 마지막 인수는 옵션으로 "False 혹은 숫자 0"을 입력하여 "강수빈"과 정확하게 일치하는 값을 찾을 수 있도록 지정한다.
- 2) 성별 찾기, 적용 수식 : =HLOOKUP ( B8, $B$2:$G$5, 3, FALSE )
- 동일하게 "강수빈" 사원의 정보 중에서 이번에는 "성별 (=3 번째 행)"을 찾아오는 수식이다.
4. Hlookup 함수 주의 사항
- 1) Vlookup 함수와 사용 방법이 동일하고 찾기를 하는 방향만 다르다 (열 방향 = Vlookup 함수, 행 방향 = Hlookup 함수)
- 2) 항상 표의 첫 행(Row)에서 찾는다.
- 3) 추출하고자 하는 값의 행은 항상 아래에 존재해야 한다.
- 4) 옵션은 거의 "일치하는 단어 찾기"이고 "False 혹은 숫자 0"을 입력해줘야 한다.
- 5) 대표 오류 2가지 : 전체 테이블은 절대참조 형식으로 할 것, 테이터 형식/공백으로 못 찾는 경우가 많다.
5. Hlookup 함수 - 다중 조건 찾기
- 1) 2개의 조건에 해당하는 텍시트를 결합(&연산자)하는 행을 추가해서 사용하는 방법
- 2) 여러 조건으로 검색 = 필터 기능 고유 기능으로 필터를 적용하여 검색하는 방법
- 먼저 필터기능을 사용하기 위해서 데이터의 형식을 가로 나열 방식에서 세로 나열 방식으로 변경이 필요하다.
- 3) Index, Match 함수를 배열 수식으로 적용하는 방법
- 일치하는 값의 위치를 찾아주는 Match 함수와 지정한 위치의 값을 가져올 수 있는 Index 함수를 배열 수식 형식으로 사용한다.
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글