엑셀에서 값을 찾을 때 VLOOKUP, HLOOKUP 함수 사용하기

공개됨 글쓴이 Admin댓글 10개

엑셀의 범위에서 값을 찾을 때는 VLOOKUP, HLOOKUP 함수를 많이 사용한다.

VLOOKUP은 Vertical Lookup을 줄여서 쓴 것으로 범위를 수직으로 내려가면서 값을 찾으며 HLOOKUP은 Horizontal Lookup을 줄여서 쓴 것으로 범위를 수평으로 따라가면서 값을 찾는다.

구문(Syntax)
VLOOKUP (lookup_value,table_array,col_index_num,[range_lookup])

범위(table_array)의 첫번째 열을 수직으로 내려가면서 키값(lookup_value)를 찾은 다음,
같은 행에 있는 지정된 열(col_index_num)의 값을 반환한다

인수 :

lookup_value : 범위에서 원하는 값을 찾기 위한 키값
table_array : 값을 찾을 범위
col_index_num : 값을 찾을 범위에서 가져올 값이 있는 열의 위치
range_lookup : 일치하는 키값을 찾을 것인지 근사값을 찾을 것인지 결정(TRUE-근사값,FALSE-일치하는 값)

엑셀함수 VLOOKUP,HLOOKUP

 

사용예 :

품목정보를 참조하여 판매실적자료 만들기

아래 그림처럼 어떤 문구회사는 품목코드별 품명단가 정보를 “품목코드” 시트에 관리하고 있다고 가정해 보자.

VLOOKUP-기준정보(품목정보)

 

이 회사의 영업직원은 판매실적자료를 품목코드별로 정리해 두었는데 아래 그림처럼 품목코드판매수량만 있고 품명단가가 없다.

VLOOKUP-다른 시트에서 값 가져오기

이 때 VLOOKUP함수를 이용하면 품명단가를 품목정보 시트에서 가져올 수 있다.

먼저 품명을 가져오기 위해 B2셀에 수식을 다음과 같이 입력한다.

=VLOOKUP(A2,품목정보!$A$2:$C$11,2,FALSE)

VLOOKUP-함수입력

 

이 수식은 다음 그림과 같은 순서로 실행된다.

VLOOKUP함수 실행순서
VLOOKUP함수 실행순서

 

수식의 실행순서를 좀 더 상세히 설명하면 다음과 같다.

1) 판매실적 시트 A2셀의 값 “A003”(함수의 1번째인수-범위에서 원하는 값을 찾기 위한 키값)을 품목정보 시트 $A$2:$C$11 범위(2번째인수-값을 찾을 범위)의 첫번째 열에서 수직으로(Vertical) 내려가면서  찾는다(Lookup)

VLOOKUP 실행순서- 키값을 찾는다.
VLOOKUP 실행순서- 키값을 찾는다.


2) “A003“을 찾은 다음, 같은 행의 2번째 열(3번째인수-값을 찾을 범위에서 가져올 값이 있는 열의 위치)의 값 “모나미 볼펜“을 가지고 온다.

VLOOKUP 실행순서- 2번째열을 가지고 온다
VLOOKUP 실행순서- 2번째열을 가지고 온다

 

4번째 인수가 FALSE이므로 키값과 정확히 일치하는 품목코드 “A003“을 찾는다.
(4번째 인수가 TRUE이고 정확히 일치하는 없을 때는 근사값을 찾는다)

 

수식이 제대로 입력되었으면 실행결과는 다음과 같을 것이다.

 

품명을 가져왔으니 이제 단가를 가져오기 위해 D2셀에 수식을 다음과 같이 입력한다.

=VLOOKUP(A2,품목정보!$A$2:$C$11,3,FALSE)

단가는 품목정보 시트의 $A$2:$C$11  범위에서 3번째에 있으므로 세번째 인수를 3으로만 바꿔주면 된다.

마지막으로 B2, D2셀의 수식을 복사해서 3행부터 9행까지 붙여 넣고 합계는 판매수량과 단가를 곱하는 수식으로 구하면 다음과 같이 최종 결과가 나올 것이다.

 

** 주의 : 만약 품목정보에서 품목코드가 첫번째 열(위 그림의 경우 A열)이 아니라 두번째, 세번째 등… 다른 열에 있다면 에러가 난다. 품목코드를 첫번째 열로 옮길 수도 되겠지만… 여럿이 공유하는 자료라서 옮길 수 없는 경우도 있다. 이럴때는 INDEX,MATCH함수를 중첩해서 써야 한다. INDEX,MATCH사용은 “VLOOKUP으로 안될때 INDEX,MATCH 사용하기” 글을 참고하자.


HLOOKUP은 VLOOKUP과 거의 유사하지만 기초자료가 아래 그림처럼 가로로 되어 있을 때 사용한다.

=HLOOKUP(A2,품목정보!F1:O3,2,FALSE)

HLOOKUP

 

 

 

[ 엑셀 찾기 및 참조영역 함수 목록 ]

엑셀에서 값을 찾을 때 VLOOKUP, HLOOKUP 함수 사용하기에 1개의 응답

  1. vlookup함수를 사용할때
    값을 찾을영역이 유동적으로 되어있어서 직접 타이핑이아니라 다른쪽에서 정보를 가져와서 인식하게끔 만들수있을까요? 머리를 아무리굴러도 찾질못했어요

    • 음…vlookup(a3,’시트2’A3:B3,2,0)
      위 와같은 형식일떄 ‘시트2’ 요 이름부분을 직접 타이핑이아닌 정해진셀에서 값을 가져와서 입력되게끔가능할까요..?

      예를들어 A1셀에 시트1로 타이핑을 하면
      vlookup(a3,’시트1’A3:B3,2,0) 로 바뀌게끔말이죠.
      안된다면 이것과 비슷한 기능을하려면 어떻게해야할까요? 이부분도 INDEX 와 MATCH 로 해결가능한가요?

      • 이 경우에는 indirect함수를 이용하면 됩니다.
        =vlookup(a3,indirect(“‘”&$a$1&”‘!”&”a3:b3”),2,0)
        홑따옴표,쌍따옴표가 섞여 있으니 주의하세요. 이 수식을 엑셀에 붙여 넣으면 따옴표의 모양이 달라 오류가 날 수 있습니다. 이 때는 PC의 키보드에 있는 따옴표로 새로 입력하면 됩니다.

  2. vlookup을 활용하면 일치하는 첫번째 값을 가져오는거 같은데, 혹시 일치하는 두번째, 세번째 값도 가져올수 있나요??

    • 안녕하세요, vlookup으로는 안되고 다음 수식으로 가능합니다.
      배열 수식입니다. 수식 입력후 ctrl + shift + Enter하셔야 합니다.
      이 수식의 마지막 인수가 1이면 첫번째, 2이면 두번째로 일치하는 것을 가져옵니다.

      =INDEX(품목정보!$B$2:$B$13,SMALL(IF(판매실적!A2=품목정보!$A$2:$A$13,ROW(품목정보!$A$2:$A$13)-ROW(품목정보!$A$2)+1),2))

      감사합니다.

  3. 안녕하세요 HLOOKUP 함수를 쓸경우 다른 파일에서 예산의 고정된 셀 값을 가져오려고 행이 추가 되서늘어날경우 고정된 셀 번호값만 가져오게 되는데 행이나 셀이 추가되어 움직여도 그 값을 가져오게 하려면 어떻게 해야 될까요?

    • 안녕하세요. HLOOKUP을 쓰는데 찾는 범위의 행이 늘어나는 상황이 어떤 상황인지 정확히 이해가 안됩니다만.. HLOOKUP은 찾는 범위가 항상 행은 고정되어 있고, 찾는 코드가 늘어나면 열만 늘어나는 구조입니다. 따라서 행이 추가되고 원래 있는 값의 행이 바뀌면, 바뀌는 대로 값을 찾아 올 수는 없습니다. 제가 제대로 이해을 못했을 수도 있으므로 해결이 안되면 파일을 admin@xlworks.net으로 보내주시면 확인해 보겠습니다.
      감사합니다.

댓글 남기기

이메일은 공개되지 않습니다.