엑셀에서 VLOOKUP, HLOOKUP 함수는 완성된 데이터에서 특정한 값을 추출하고자 할 때 사용됩니다. 이 두 함수는 한 번만 배워두면 엑셀을 사용할 때 두고두고 써먹을 수 있는 함수입니다. 국산 스프레스시트 프로그램인 한셀에서도 이 두 함수를 지원합니다. 한셀에서의 사용 방법은 이 글에서 설명하고 있는 엑셀에서의 사용 방법과 동일합니다.

같이 보면 좋은 글
2017/07/29 [엑셀] 사용설명서 1. 조건부 서식 by Walter Erzsamatory

VLOOKUP 함수와 HLOOKUP 함수

이 두 함수는 완성된 데이터에서 사용자가 원하는 값을 추출할 때 사용됩니다. 쉬운 설명을 위하여 다음과 같은 데이터가 있다고 가정하겠습니다. 여기에서 성명에 따른 계급을 구하고자 할 때 VLOOKUP 함수를 사용하면 됩니다. VLOOKUP 함수는 첫 번째 열에서 사용자가 지정한 값과 일치하는 행을 찾은 후 그 행에서 사용자가 지정한 열 번호에 있는 값을 추출하는 함수입니다.

그렇다면 HLOOKUP 함수는 VLOOKUP 함수와 무엇이 다를까요? HLOOKUP 함수는 VLOOKUP 함수가 첫 번째 열에서 값을 검색했던 것과는 달리 첫 번째 행에서 값을 검색한 후 사용자가 지정한 행 번호에 있는 값을 추출하는 함수입니다. 아래의 데이터를 이용하여 설명하자면, HLOOKUP 함수는 순번, 소속, 계급 등 타이틀 행에서 값을 검색한 후 일치되는 값이 있으면 그 열에서 사용자가 지정한 행 번호의 값을 추출합니다.

VLOOKUP 함수와 HLOOKUP 함수의 사용법은 동일하기 때문에 VLOOKUP 함수에 대해서 잘 알고 있다면 HLOOKUP 함수 사용법을 배우는 것은 매우 쉽습니다. 다시 한 번 더 말씀드리자면, 위 그림에서 볼 수 있는 것과 같이 VLOOKUP과 HLOOKUP의 차이점은 지정 값의 검색 방향에 있습니다. VLOOKUP은 열을 검색하고 HLOOKUP은 행을 검색한다는 것이 다릅니다.

VLOOKUP 함수 사용법

VLOOKUP 함수 사용법은 다음과 같습니다.

=VLOOKUP(① 찾는 값, ② 데이터 테이블 범위, ③ 열 번호, ④ 완전일치여부)

데이터 ② 테이블 범위의 첫 번째 열에서 ① 찾는 값을 검색한 후 일치하는 값이 있으면 사용자가 지정한 ③ 열 번호에 있는 값을 반환합니다. 이 때 유사하게 일치하는 값을 찾도록 지정할 수도 있는데요. ④ 완전일치여부에 TRUE를 입력하면 유사하게 일치하는 값을 찾아 지정된 행의 값을 반환합니다. 반대로 FALSE 를 입력하면 완전히 일치되는 값만을 찾습니다.

위 예제에서 볼 수 있듯이 5번 근무자를 검색하려고 합니다. 왼쪽 데이터 테이블을 보면 5번 근무자는 헌병반 상병 김준현입니다. 그냥 쉽게 이렇게 찾을 수도 있겠으나 데이터의 양이 방대해지면 사람이 직접 찾는 것이 어려울 수도 있습니다.

또한 엑셀을 활용하여 자동화된 문서를 만들고자 하는경우 VLOOKUP 함수는 매우 유용하게 사용될 수 있습니다. 이러한 이유 때문에 VLOOKUP 함수가 존재하는 것이구요. 그럼 VLOOKUP을 이용하여 5번 근무자를 찾아보도록 하겠습니다. 먼저 위에서 소개했던 VLOOKUP 함수 사용법을 생각한다면 다음과 같은 함수식이 만들어집니다.

=VLOOKUP(① 5, ② $B$3:$F$13, ③ 4, ④ FALSE)

① 5를 입력한 이유는 찾고자 하는 값이 5번 근무자 즉, 5 이기 때문입니다.

② 데이터 테이블 범위를 입력하면 됩니다. 여기에서는 B3:F13이 됩니다. 그런데 왜 각 문자마다 $ 를 넣은 것일까요? 그 이유는 여기에서 사용된 데이터 테이블 범위는 변하지 않고 고정되어야 하기 때문입니다. 위 예제에서처럼 한 개의 값만 찾는다면 굳이 $ 를 추가할 필요는 없지만, 일반적으로 VLOOKUP 함수는 자동 채우기 기능을 이용하여 응용이 되기 때문에 데이터 테이블 범위를 고정시킬 필요가 있습니다. 즉, 자동 채우기 때문에 VLOOKUP 함수의 데이터 테이블 범위가 변동되는 것을 막는 것이 필요합니다. 왜냐하면 엑셀에서 자동 채우기를 하면 자동으로 열 문자가 하나씩 늘어나고 행 번호도 1씩 늘어나기 때문입니다.

③ 추출하고자 하는 열 번호를 입력합니다. 데이터 테이블 범위를 기준으로 맨 앞에 있는 열이 1번이 됩니다. 따라서 위 예제에서 이름을 추출하고자 하는 경우에는 4 를 입력하면 됩니다.

④ 완전일치여부를 입력합니다. 여기에서는 FALSE를 입력하여 완전히 일치하는 경우에만 값을 추출하도록 했습니다. 만약 TRUE를 입력하는 경우에는 완전히 일치되는 값이 없더라도 가장 유사한 값을 찾은 후 해당 행에서 값이 추출됩니다.

위 수식의 결과는 다음과 같습니다. 검색하고자 했던 5번 근무자인 김준현이 셀에 잘 표시되고 있음을 알 수 있습니다.

VLOOKUP 함수의 간단한 사용 방법에 대하여 알아보았습니다. VLOOKUP 함수에 대해서 다시 간단히 설명하자면, 지정한 데이터 테이블의 첫 열에서 입력된 값을 검색한 후, 일치되는 값이 있는 경우, 해당 행에서 사용자가 지정한 열 번호에 있는 값을 추출하는 것이 바로 VLOOKUP 함수입니다.

이와는 반대로 HLOOKUP 함수는 열이 아닌 행을 기준으로 작동하는 함수입니다. 지금부터는 VLOOKUP 함수의 사용 예제에 대해서 살펴보도록 하겠습니다. 사용 예제를 통하여 VLOOKUP 함수를 더 쉽게 이해할 수 있을 것입니다.

응용 예제 1: 병사의 계급을 알아내는 방법

이 예제에서는 위에서 사용했던 데이터를 그대로 이용하겠습니다. 이번에는 상황을 변경하여, 경계 병력 근무 편성 업무를 담당하는 병사가 경계 병력 이름에 따른 근무지를 구하고자 한다고 가정합시다.

여기에서 찾고자 하는 병력의 이름은 고정된 것이 아니라 사용자가 직접 입력해야 하는 것이기 때문에 위에서 살펴보았던 방법을 사용할 수는 없습니다. 이 때에는 VLOOKUP 함수를 조금 응용하는 방법을 사용해야 합니다. 실무에서는 이 방법이 많이 사용됩니다.

먼저 근무지가 입력되어야 하는 셀 즉, H10 셀에 병력 이름에 따른 근무지를 검색하는 VLOOKUP 함수가 입력되어야 합니다. VLOOKUP 함수는 다음과 같이 구성됩니다.

=VLOOKUP(① H9, ② $E$3:$F$13, ③ 2, ④ FALSE)

① 사용자가 병력 이름을 입력하는 H9 셀을 지정합니다. 첫 번째 예시와는 달리 고정된 값이 아니라 셀 영역이 입력되었다는 것에 주목해야 합니다. VLOOKUP 함수에서 검색 값이 반드시 고정되어야 하는 것은 아닙니다. 즉, 셀 영역을 입력하여 사용자가 임의의 값을 입력하도록 할 수도 있습니다.

② 첫 번째 열이 병력 이름이 되도록 셀 영역을 지정해줍니다. 여기에서는 E3:F13 을 지정하면 됩니다. VLOOKUP 함수는 데이터 테이블 영역에서 첫 번째 열을 검색하는 함수이기 때문에 첫 번째 예제에서처럼 모든 데이터 테이블 영역을 지정하면 안 됩니다. 첫 번째 열에는 반드시 검색할 값이 들어간 열이 와야 합니다.

③ 지정된 데이터 테이블 영역에서 근무지의 열 번호를 입력합니다. 여기에서는 2 를 입력했습니다.

④ 병력 이름이 반드시 일치해야 하므로 여기에서는 FALSE 를 입력했습니다.

위 수식의 결과는 다음과 같습니다. 사용자가 검색하길 원하는 병력 이름을 성명 란에 입력하면 근무지 셀이 해당되는 병사의 근무지로 자동으로 바뀌는 것을 확인할 수 있습니다. 예제에서는 데이터가 많지 않기 때문에 사람이 직접 찾는 것이 더 빠르겠지만 병력 데이터가 매우 방대해지는 경우에는 이 방법이 매우 유용하게 사용될 수 있을 것입니다. 그리고 위에서도 언급했듯이 이와 같은 방법을 사용하면 엑셀 파일을 이용하여 자동화된 문서를 만들 수도 있습니다. 대부분의 엑셀 프로그램에서는 VLOOKUP 함수가 사용되고 있습니다.

지금까지 VLOOKUP 함수의 간단한 응용 예제에 대하여 알아보았습니다. 이 예제에서는 간단하게 VLOOKUP 함수를 사용하여 데이터를 검색하는 방법에 대해서만 설명했지만, VLOOKUP 함수는 이것 이외에도 응용할 수 있는 분야가 매우 무궁무진하게 많습니다.

하지만 이 모든 것들을 이 글에서 설명을 하게되면 시간이 너무나 오래 걸리고 특히 글의 길이가 길어져 방문자가 글을 읽는 것이 힘들어지기 때문에 일단은 여기에서 글을 마감하도록 하겠습니다. 기타 다양한 응용 방법은 추후에 새로운 글로 작성하도록 하겠습니다.

마무리

지금까지 VLOOKUP 함수와 HLOOKUP 함수의 사용 방법은 어떻게 되는지, 실제로 VLOOKUP 함수가 어떻게 사용되고 있는지 알아보았습니다.

이 글에서 설명했던 VLOOKUP 함수의 예제는 매우 간단했지만 사실은 그것으로도 매우 훌륭한 기능을 하고 있습니다. 왜냐하면 이 기능은 VLOOKUP 함수를 이용하지 않으면 구현할 수 있는 것이 아니기 때문입니다. 엑셀을 사용하지 않았다면 사람이 직접 눈으로 확인했어야 하는 작업들을 엑셀에서는 단 몇 초만에 끝낼 수 있다는 것은 엑셀이 얼마나 강력한 프로그램인가를 보여줍니다.

하지만 이 글에서는 HLOOKUP 함수가 실제로 어떻게 사용되는지 알아볼 수 있는 예제에 대해서는 설명하지 않았습니다. 왜냐하면 VLOOKUP 함수와 HLOOKUP 함수의 사용 방법은 동일하기 때문에 VLOOKUP 함수 사용 예제만 충분히 공부하더라도 HLOOKUP 함수 사용 방법을 익히는데에는 무리가 없기 때문입니다. 이 두 함수는 엑셀에서 자주 사용되므로 지금 알아두면 언젠가 유용하게 사용될 수 있을 것입니다.