19. 엑셀[Excel] 알아두면 편리한 "VLOOKUP과 HLOOKUP"

19. 엑셀[Excel] 알아두면 편리한 "VLOOKUP과 HLOOKUP"

 

 

안녕하십니까. 경리 회계 경력 20년 차 "껌블유"입니다.

재고 관리, 견적 관리, 주문 관리 등에서 꼭 쓰이는 VLOOKUP 함수와 HLOOKUP 함수에 대해 포스팅하고자 합니다. 복잡하고 많은 데이터 안에서 VLOOKUP 함수와 HLOOKUP 함수를 이용하여 편하고 쉽게 업무 단축에 도움이 되셨으면 좋겠습니다.

 

 

1. "VLOOKUP" 함수

VLOOKUP 함수를 사용하기 위해 아래 첨부사진을 보시면, 메뉴판이라는 품목과 단가 금액 표를 만들고, 옆 열에 주문표를 작성하였습니다. 주문표 안에 들어갈 금액을 넣기 위해 VLOOKUP 함수를 이용하여 품목에 맞는 금액을 넣어보겠습니다. 입력방식은 =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])으로 찾아줄 값, 찾을 값이 들어있는 전체의 범위, 들어갈 값이 들어있는 열의 순번이라고 생각하시면 됩니다. 먼저, 금액 열에 들어갈 셀 안에 수식을 입력하기 위해 F2 편집 단축키를 누르고, "=" 입력 후 VLOOKUP(으로 직접 입력 또는 VL까지만 입력 후 자동완성창에서 함수를 더블클릭하여 선택해줍니다. 찾아줄 품목 E3의 돈가스 셀을 선택하고 콤마(,), 이 돈가스 등의 단가가 들어간 메뉴판 전체를 드래그하여 범위를 선택하고 콤마(,), 메뉴판의 품목 열을 1열, 금액 열을 2 열이라고 생각하고 금액을 넣어야 하므로 금액 열인 숫자 2를 입력 후 콤마(,), 정확하게 일치하는 값을 넣기 위해 FALSE라고 직접 입력 또는 자동완성창에서 더블클릭하여 선택한 후 괄호를 닫고 엔터를 칩니다. 이때 수식을 복사하여 아래로 끌 경우 범위가 달라지기 때문에 메뉴판의 범위인 A2:B8을 드래그하여 선택한 후 F4 고정 단축키를 눌러 $A$2:$B$8 가 되도록 범위를 고정하여 수식을 완성합니다. 완성된 수식이 입력된 셀을 선택하고 작은 점을 잡고 드래그하여 복사하거나 더블클릭하여 수식을 채워주면 각각의 품목의 맞는 금액을 메뉴판의 범위 안에서 찾아 금액이 알맞게 들어간 부분을 확인할 수 있습니다.

 

 

 

2. "HLOOKUP" 함수

HLOOKUP 함수는 VLOOKUP 함수와 기능이 비슷한 함수로 메뉴판처럼 위에서 아래로 쓰여있는 범위를 가지고 수식을 만드는 VLOOKUP함수가 있고, 왼쪽에서 오른쪽으로 입력되어있는 범위를 가지고 수식을 만들 수 있는 함수는 HLOOKUP 함수를 이용합니다. HLOOKUP함수 사용 예제를 만들기 위해 위에서 아래로 되어있는 메뉴판의 범위를 복사하여 오른쪽 붙여 넣기 할 비어있는 셀을 선택한 후 선택하여 붙여 넣기를 이용하여, 행/열 바꿈을 선택한 후 가로 형식의 메뉴판을 만들었습니다. 이처럼 HLOOKUP함수는 가로 형식의 범위를 이용하여 수식을 입력합니다.

이제 VLOOKUP 함수의 사용과 마찬가지로 V를 H로만 바꾸기만 하면 됩니다. 입력방식은 =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])으로 찾아줄 값, 찾을 값이 들어있는 전체의 범위, 들어갈 값이 들어있는 행의 순번이라고 생각하시면 됩니다. 금액이 들어갈 셀을 선택하고 F2 단축키를 누른 후 "=HLOOKUP("라고 입력 또는 HL까지만 입력하여 자동완성 창에서 함수를 더블클릭하여 선택합니다. 마찬가지로 찾아줄 품목의 셀을 선택한 후 콤마(,), 범위를 지정한 후 콤마(,) 1행과 2행이라고 생각하고 금액이 있는 2행을 선택해줘야 하므로 숫자 2를 입력하고 콤마(,), 정확하게 일치하는 값을 넣어야 하므로 FALSE라고 직접 입력하거나 자동완성창에서 더블클릭한 후 괄호를 닫고 엔터를 치면 수식은 완성됩니다. 범위 고정을 위해 범위가 입력된 문구를 드래그하여 F4 고정 단축키로 고정한 후 수식을 복사하면 됩니다.