엑셀 vlookup 예제, hlookup 예제를 통한 data 찾기 방법

안녕하세요. 공돌이 입니다.

엑셀을 사용하다 보면 찾고 싶은 항목이 생기고, 그 해당 항목에 대한 결과 값을 찾고 싶을 때가 있습니다. 물론 ctrl + f로 찾기 기능을 활용하여 원하는 값을 찾아서 수작업으로 입력을 할 수 있습니다. 다만 해당 방법으로 업무를 하게 되었을 경우 방대한 data를 처리하기에는 많은 시간, 노력, 끈기가 필요하며 자연스럽게 야근을 하게 되는 상황이 발생하게 됩니다. 엑셀에서는 vlookup과 hlookup이라는 함수를 제공하고 있습니다.이러한 함수를 이용하면 어떤 구조에서 라도 원하는 값을 찾아 그 결과 값을 보고 또 이를 이용하여 추가적인 계산이 가능합니다. 예를 들면 특정 학생의 평균 성적을 도출하거나, 특정 과목만의 평균을 계산하거나, 특정 상품의 금액들만 모아서 보거나 등 다양하게 응용이 가능합니다.

그러면 vlookup과 hlookup에 대한 예시와 함께 해당 함수에 대해서 포스팅을 시작해 보겠습니다. 먼저 vookup과 hlookup은 수행 작업 자체는 같습니다. 사용자가 찾고 싶은 내용에 대해서 일치하는 셀을 찾고, 이에 해당하는 특정 위치의 data를 반환하는 것입니다. 그러나 vlookup은 vertical 방향 (수직 방향) 으로 data를 검색하는 함수이고, hlookup은 horizontal 방향 (수평 방향)으로 data를 검색하는 함수입니다. 아래 예제를 보면 쉽게 이해하실 수 있으니, 아래 예제들과 함께 보시겠습니다.

먼저 vlookup입니다. 아래 그림처럼 각 학생에 대해 점수표를 만들었습니다.

 

위에서 언급한 수직 방향으로의 검색이란 이름과 점수가 전부 수직 방향으로 정렬이 되어 있습니다. 즉 빨간색 화살표처럼 엑셀이 검색을 하면서 사용자가 찾고자 하는 셀을 검색하게 됩니다. 아래는 vlookup함수 안에 기입을 해야 하는 내용입니다.

 

VLOOKUP(내가 찾고 싶은 이름, 검색하고 싶은 범위, 출력할 결과물, FALSE)

순으로 입력을 하시면 됩니다. 저는 이순신의 국어와 수학 점수를 가져오는 것으로 엑셀을 구성하였고, 아래와 같이 작성하였습니다.

F2에 이순신이라는 이름을 넣었고 A2:B7이라는 범위에서 이름을 찾으라는 명령을 내렸습니다. 그 다음 출력할 결과물입니다. 저는 국어 결과에는 2를 넣었고, 수학 결과에는 3을 넣었습니다. A를 기준으로 A에는 이름이, B에는 국어, C에는 수학이 있습니다. 즉 A는 1, B는 2, C는 3이 됩니다. 항상 range의 첫 번째 열이 1이 됩니다. 따라서 만약 range의 시작이 A2:B7이 아니라 B2:D7이었다면 B열이 결괏값 도출을 위해 1이 됩니다. 그 다음 false라고 되어 있는데요. 이것은 찾고자 하는 항목과 range안의 항목이 정확히 일치했을 때 결과를 도출할 것이냐를 묻는 부분입니다. False로 선택을 하셔야 정확히 일치한 값이 나오게 됩니다. 항상 false로 설정하는 것을 잊지 말아 주세요.그림의 화살표처럼 vlookup은 열 방향 즉 수직 방향으로 이름을 먼저 검색하고 이후 행 방향으로 데이터를 찾아가는 함수입니다. 여기서 주의해야 하는 점은 반드시 range의 시작점이 내가 찾고자 하는 값의 첫 번째 열에 있어야 합니다. 예를 들어 수학 100점을 맞은 사람은 누구인지를 검색 검색 싶을 때 vlookup을 사용할 수 있습니다. 하지만 위의 표를 그대로 적용하면 찾을 수가 없습니다. E열에 새롭게 이름 tab을 만들어야 합니다. 그리고 range를 C2:E7로 해야 합니다.

 

다음은 hlookup 입니다. Vlookup과 data 구조가 다름을 알 수 있습니다.

화살표로 표시한 것처럼 이름이 가로로 배치가 되어 있기 때문에 더 이상 vlookup으로는 이름으로 점수를 찾을 수가 없습니다. 하지만 수평방향으로 이름을 검색하고 수직방향으로 data를 찾으면 되는 것이기에 hlookup을 사용하면 원하는 점수를 추출할 수 있습니다. 아래 코드가 그 결과입니다.

 

Vlookup과 내용은 동일합니다. 다만 행 방향의 점수를 사용자에게 뿌려주는 차이만 있습니다. Hlookup도 vlookup에서 언급한 range에 대한 주의사항 부분에 대해서도 동일하게 적용이 됩니다. 항상 찾고자 하는 기준이 range의 시작이 되어야 합니다.

이렇게 엑셀에서 데이터를 찾아 해당 관련된 data를 출력하는 예제에 대해서 알아보았습니다. Vlookup과 hlookup은 매우 유용한 함수이지만 그 사용법이 조금 까다로워 잘 못 쓰고 있는 경우가 많이 이렇게 내용을 공유하게 되었습니다. 표의 구조는 사용자에 따라 그리고 상황에 따라 보기 좋은 형태가 존재하기 때문에 vlookup 하나만 아는 것보다 hlookup을 함께 알고 있는 것이 data 분석 시 큰 도움이 될 것 같아 두 개를 동시에 소개했습니다.

도움이 되면 좋겠습니다.

감사합니다.