행 방향 참조 함수, HLOOKUP
이전글에서 엑셀 함수의 꽃이라 불리는 VLOOKUP 함수를 배웠습니다.
VLOOKUP 은 찾을 값이 가장 좌측에 위치한 참조 범위의 열 방향 우측에 있는 값을 찾는 함수 입니다.
이번 글에서는 행 방향 참조 함수 HLOOKUP 함수에 대해 배워보겠습니다.
엑셀에서 값을 참조하기 위해 가장 많이 사용하는 함수는 바로 VLOOKUP 입니다.
HLOOKUP 함수는 VLOOKUP 에 비하면 사용도는 떨어지지만, 참조의 방향이 다를 뿐 동일한 방식으로 동작하는 참조 함수 입니다.
VLOOKUP 함수를 사용하는 경우와 HLOOKUP 함수를 사용하는 경우를 직관적으로 느낄 수 있을 정도로 연습이 필요합니다.
VLOOKUP 함수에 익숙해진 사용자는 HLOOKUP 함수도 어렵지 않게 접근 가능할 것입니다.
함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(HLOOKUP)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.
위의 그림에서 =VLOOKUP( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (lookup_value, table_array, row_index_num, [range_lookup]) 의 의미는 아래와 같습니다.
lookup_value : 찾을 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
table_array : 찾을 값(lookup_value)과 참조할 값이 포함된 범위 주소를 두 번째 인수로 입력해야 합니다. (필수 입력)
row_index_num : 참조할 값이 참조 범위 주소의 몇 번째 행에 위치하고 있는지를 세 번째 인수로 입력해야 합니다. (필수 입력)
[range_lookup] : FALSE 면 정확히 일치하는 값, TRUE 또는 생략하면 비슷하게 일치하는 값을 찾는 찾기 옵션을 네 번째 인수로 입력해야 합니다. (선택 입력)
왼쪽의 표에 학생의 이름과 나이 정보가 포함된 목록이 있습니다.
해당 목록에 있는 학생의 나이에 맞는 학년 정보를 검색하여 참조해 보겠습니다.
행 방향 참조 함수, HLOOKUP 자세히 살펴보기
D3 셀에 =HLOOKUP(C3,$G$2:$L$3,2,TRUE) 수식을 입력합니다.
그러면 D3 셀에 “3학년” 이 출력됩니다.
김민준 학생은 나이가 10세 이고 오른쪽의 표에서 보면 3학년에 해당되는 것을 알 수 있습니다.
D3 셀에 입력된 참조 함수 수식을 분석해 보겠습니다.
- 첫 번째 인수 lookup_value C3 는 찾을 값 입니다. 즉, 찾을 학생의 나이 값이 입력되어 있는 참조 주소 입니다.
- 두 번째 인수 table_array $G$2:$L$3 는 찾을 값이 포함된 테이블의 참조 범위 주소 입니다.
이 때 주의할 점은 lookup_value 의 값이 포함되어 있는 행이 가장 위쪽 위치하는 범위로 지정해야 한다는 것 입니다.
위의 예제는 학생의 나이가 lookup_value 이기 때문에 나이 행이 가장 위쪽에 위치하도록 2 행 부터 table_array 의 범위를 지정해야 한다는 것 입니다.
즉, 찾을 값과 참조할 값이 포함된 범위인 $G$2:$L$3 을 table_array 로 지정하면 됩니다.
참조 범위는 절대주소로 지정하는 것이 좋습니다. 수식을 복사하여 다른 셀에 적용시 참조 범위가 의도치 않은 형태로 변경될 수 있기 때문입니다. - 세 번째 인수 row_index_num 은 참조할 값이 위치한 행의 위치 값을 의미합니다.
참조 범위인 $G$2:$L$3 에서는 2 행에 위치한 값은 1, 3 행에 위치한 값은 2의 값을 갖습니다. - 네 번째 인수 [range_lookup] 은 값을 찾는 방식에 대한 옵션 입니다.
선택적으로 입력되는 인수이며 생략시 TRUE 가 입력된 것과 동일하게 동작합니다.
학생의 나이는 숫자 이루어진 값이며 완전히 일치하는 경우를 찾고 있기 때문에 FALSE 를 입력하면 됩니다.
참조 범위의 가장 위쪽 행에서 나이가 10인 열을 찾아서 두 번째 행에 위치한 값을 출력하라는 함수 입니다.
입력된 수식이 올바르게 동작하여 D3 셀에 김민준 학생의 학년 값 “3학년” 이 출력된 것 입니다.
행 방향 참조 함수, HLOOKUP 오류
찾으려는 값 lookup_value 가 참조 범위 table_array 에 있는 경우에는 정상적인 결과가 출력됩니다.
만약, 찾으려는 값이 참조 범위에 존재하지 않는 값이라면 어떻게 될까요?
HLOOKUP 함수의 네 번째 인수 range_lookup 값을 TRUE 로 설정하면 어떻게 될까요?
range_lookup 값을 TRUE 로 사용할 때는 참조범위의 값이 오름차순으로 정렬되어 있는 것이 좋습니다.
위의 예제에서는 나이 값이 오름차순으로 정렬되어 있기 때문에 그대로 진행해도 괜찮습니다.
range_lookup 값을 TRUE 로 설정해도 결과는 동일합니다.
오류를 발생시키기 위해 서경준 학생의 나이 C8 셀에 참조 범위에 존재하지 않는 나이 값 “14” 을 입력해 보겠습니다.
“#N/A” 오류가 발생할 것을 예상했는데, 오류가 발생하지 않고 “6학년” 그대로 출력되고 있습니다.
참조 범위에 존재하지 않는 값을 검색했는데 왜 오류가 발생하지 않았을까요?
정답은 range_lookup 값을 TRUE 로 변경했기 때문입니다.
찾는 값이 숫자이고 range_lookup 값이 TRUE 인 경우에는, 찾는 값이 없을 경우 그와 가장 유사한 값으로 출력됩니다.
이 상태에서 C8 셀의 함수 수식의 range_lookup 값을 다시 FALSE 로 변경하면 어떻게 될까요?
“#N/A” 오류가 발생 됩니다. 즉, 정확히 일치하는 값이 존재하지 않는다는 뜻 입니다.
행 방향 참조 함수, HLOOKUP 오류 해결 방법
오류를 해결하는 방법은 무엇일까요?
네 개의 인수를 천천히 살펴보는 것이 그 방법 입니다.
VLOOUP 함수 수식이 입력된 셀에서 F2(펑션키)를 누르면 현재 수식이 참조하고 있는 값들을 눈에 띄는 다양한 색상으로 표시해 줍니다.
가장 먼저 두 번째 인수 table_array 참조 범위를 확인해야 합니다.
그 다음으로 세 번째 인수 row_index_num 열 번호를 확인해야 합니다.
그 다음으로 확인할 것이 첫 번째 인수 lookup_value 찾을 값 입니다.
마지막으로 확인할 것이 네 번째 인수 range_lookup 인수 입니다.
정확히 일치하는 값이 없을 경우 찾지 않을 것인지, 가장 유사한 값을 찾을 것인지 결정해야 합니다.
오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.
각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.
엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.