[Excel] VLOOKUP과 HLOOKUP 함수 사용하기

Excel에서 데이터를 검색하고 참조할 때 VLOOKUP과 HLOOKUP 함수는 매우 유용합니다. 이 두 함수는 테이블에서 특정 값을 찾고, 해당 값의 행 또는 열에 있는 데이터를 반환하는 데 사용됩니다. 여기서는 VLOOKUP과 HLOOKUP 함수의 개념을 상세히 설명하고, 예제 데이터를 사용해 어떻게 사용하는지 보여드리겠습니다.

 

 

1. VLOOKUP 

VLOOKUP을 사용해야 하는 상황

VLOOKUP 함수는 특히 대규모 데이터베이스에서 특정 값을 찾고 해당 값과 연관된 데이터를 빠르게 가져올 때 유용합니다. 다음과 같은 상황에서 VLOOKUP을 사용하는 것이 적합합니다.

• 데이터 매칭 및 병합: 두 개 이상의 테이블에서 공통된 키 값을 기준으로 데이터를 결합해야 할 때.
• 조건부 데이터 조회: 특정 조건에 맞는 데이터를 조회해야 할 때.
• 보고서 생성: 특정 기준에 따라 데이터를 정리하고 요약해야 할 때.
• 자동 채우기: 특정 데이터를 자동으로 채워야 할 때.

1) 예시: 제품 코드로 가격 찾기

가령, 제품 목록과 가격 정보가 있는 데이터베이스가 있다고 가정해보겠습니다. 고객 주문 시 제품 코드로 해당 제품의 가격을 자동으로 찾아서 총 주문 금액을 계산해야 하는 상황입니다.

제품 목록

	A	B	C	D
1	제품 코드	제품명	카테고리	가격
2	P001	노트북	전자제품	1,000,000
3	P002	스마트폰	전자제품	800,000
4	P003	태블릿	전자제품	600,000
5	P004	모니터	전자제품	300,000

고객 주문

	A	B	C	D	E	F
1	주문 번호	고객명	제품 코드	수량	단가	총 주문 금액
2	O1001	김철수	P001	2
3	O1002	이영희	P003	1
4	O1003	박민수	P002	3
5	O1004	최지우	P004	5

3) 예시: 주문 데이터에 제품 가격 추가하기

고객 주문 시 제품 코드로 해당 제품의 가격을 찾아와서 총 주문 금액을 계산하려면 다음과 같이 VLOOKUP을 사용할 수 있습니다.

제품 가격 찾기

=VLOOKUP(D2, 제품목록!$A$2:$D$5, 4, FALSE)

총 주문 금액 계산

=VLOOKUP(D2, 제품목록!$A$2:$D$5, 4, FALSE) * E2

완성된 주문 데이터

	A	B	C	D	E	F
1	주문 번호	고객명	제품 코드	수량	단가	총 주문 금액
2	O1001	김철수	P001	2	1,000,000	2,000,000
3	O1002	이영희	P003	1	600,000	600,000
4	O1003	박민수	P002	3	800,000	2,400,000
5	O1004	최지우	P004	5	300,000	1,500,000

VLOOKUP 사용 시 주의사항

• 정확한 일치 여부: range_lookup 인수를 FALSE로 설정하여 정확한 값을 찾도록 합니다.
• 첫 번째 열 위치: 찾고자 하는 값이 테이블의 첫 번째 열에 있어야 합니다.
• 참조 범위 고정: 참조 범위를 고정하여 데이터 검색 범위가 변경되지 않도록 합니다(예: $A$2:$D$5).

VLOOKUP 함수는 데이터를 빠르고 효율적으로 검색하고, 여러 테이블 간 데이터를 연결할 때 매우 유용합니다. 위의 예시처럼 제품 코드로 가격을 찾아 총 주문 금액을 계산하는 등의 작업에서 유용하게 사용할 수 있습니다.

이제 VLOOKUP의 개념과 사용 방법을 이해하셨으니, 실제 데이터 분석 작업에 적용해 보세요. 필요한 경우 Excel 파일을 다운로드하여 직접 실습해볼 수도 있습니다.

 

 

 

2. HLOOKUP 

HLOOKUP을 사용해야 하는 상황

HLOOKUP 함수는 가로 방향으로 값을 검색하고 해당 값과 연관된 데이터를 빠르게 가져올 때 유용합니다. 다음과 같은 상황에서 HLOOKUP을 사용하는 것이 적합합니다.

• 가로로 배열된 데이터 검색: 데이터가 행으로 정렬되어 있고, 특정 값을 기준으로 행을 검색해야 할 때.
• 조건부 데이터 조회: 특정 조건에 맞는 데이터를 가로 방향으로 조회해야 할 때.
• 보고서 생성: 특정 기준에 따라 가로 방향으로 데이터를 정리하고 요약해야 할 때.

1) 예시: 제품 코드를 기준으로 제품명 찾기

가령, 제품 코드와 관련된 정보가 가로 방향으로 배열된 데이터베이스가 있다고 가정해보겠습니다. 고객이 주문할 때 제품 코드로 제품명을 찾아야 하는 상황입니다.

제품 목록

	A	B	C	D	E
1	제품 코드	P001	P002	P003	P004
2	제품명	노트북	스마트폰	태블릿	모니터
3	카테고리	전자제품	전자제품	전자제품	전자제품
4	가격	1,000,000	800,000	600,000	300,000

고객 주문

	A	B	C	D	E
1	주문 번호	고객명	제품 코드	수량	제품명
2	O1001	김철수	P001	2
3	O1002	이영희	P003	1
4	O1003	박민수	P002	3
5	O1004	최지우	P004	5

2) 예시: 주문 데이터에 제품명 추가하기

고객 주문 시 제품 코드로 해당 제품의 이름을 찾아와야 하는 경우, 다음과 같이 HLOOKUP을 사용할 수 있습니다.

제품명 찾기

=HLOOKUP(D2, 제품목록!$A$1:$E$4, 2, FALSE)

완성된 주문 데이터

	A	B	C	D	E
1	주문 번호	고객명	제품 코드	수량	제품명
2	O1001	김철수	P001	2	노트북
3	O1002	이영희	P003	1	태블릿
4	O1003	박민수	P002	3	스마트폰
5	O1004	최지우	P004	5	모니터

 

HLOOKUP 사용 시 주의사항

• 정확한 일치 여부: range_lookup 인수를 FALSE로 설정하여 정확한 값을 찾도록 합니다.
• 첫 번째 행 위치: 찾고자 하는 값이 테이블의 첫 번째 행에 있어야 합니다.
• 참조 범위 고정: 참조 범위를 고정하여 데이터 검색 범위가 변경되지 않도록 합니다(예: $A$1:$E$4).

결론

HLOOKUP 함수는 데이터를 가로 방향으로 빠르고 효율적으로 검색하고, 여러 테이블 간 데이터를 연결할 때 매우 유용합니다. 위의 예시처럼 제품 코드로 제품명을 찾아서 주문 데이터에 추가하는 등의 작업에서 유용하게 사용할 수 있습니다.

이제 HLOOKUP의 개념과 사용 방법을 이해하셨으니, 실제 데이터 분석 작업에 적용해 보세요. 필요한 경우 Excel 파일을 다운로드하여 직접 실습해볼 수도 있습니다.