Excel 2016에서 VLOOKUP 및 HLOOKUP 함수를 사용하는 방법

Excel 2016 조회 기능 중 가장 많이 사용되는 기능은 HLOOKUP(가로 조회용) 및 VLOOKUP(수직 조회용) 기능입니다. 이러한 함수는 리본 메뉴의 수식 탭에 있는 조회 및 참조 드롭다운 메뉴와 함수 삽입 대화 상자의 조회 및 참조 범주에 있습니다. 데이터 테이블에서 값을 조회하여 값을 반환할 수 있는 강력한 함수 그룹의 일부입니다.

VLOOKUP 함수는 프로그램이 찾고 있는 값과 일치하거나 초과하는 값을 찾을 때까지 조회 테이블의 가장 왼쪽 열을 수직으로(위에서 아래로) 검색합니다. HLOOKUP 함수는 찾고 있는 값과 일치하거나 초과하는 값을 찾을 때까지 조회 테이블의 맨 위 행을 가로로(왼쪽에서 오른쪽으로) 검색합니다.

VLOOKUP 함수는 다음 구문을 사용합니다.

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

HLOOKUP 함수는 거의 동일한 구문을 따릅니다.

HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

두 함수에서 lookup_value 인수는 조회 테이블에서 조회하려는 값이고 table_array 는 조회할 값과 반환할 관련 값을 모두 포함하는 조회 테이블의 셀 범위 또는 이름입니다.

col_index_num의 인수의 값에 기초하여 매칭 VLOOKUP 함수에 의해 리턴 된 값을 포함하는 룩업 테이블의 열에 나타내고 lookup_value table_array의 인자에 대하여 그 인수. 이 열이 세로 조회 테이블의 첫 번째 열에서 오른쪽으로 몇 열인지를 계산 하여 col_index_num 인수를 결정하고 조회 테이블의 첫 번째 열을 이 개수에 포함합니다.

row_index_num의 인수 값을 포함하는 행이 반환 나타내고 수평 표의 HLOOKUP 함수. 수평 조회 테이블의 맨 위 행에서 이 행 아래로 몇 개의 행이 있는지 계산 하여 row_index_num 인수를 결정 합니다. 다시 말하지만, 이 개수에 조회 테이블의 맨 위 행을 포함합니다.

VLOOKUP 및 HLOOKUP 함수에 col_index_num 또는 row_index_num 인수를 입력할 때 입력하는 값은 조회 테이블의 총 열 또는 행 수를 초과할 수 없습니다.

VLOOKUP 및 HLOOKUP 함수 의 선택적 range_lookup 인수는 Excel 에서 table_array 의 lookup_value 에 대해 정확히 일치하는지 또는 대략적으로 일치하는지 여부를 지정하는 논리적 TRUE 또는 FALSE입니다 . VLOOKUP 또는 HLOOKUP 함수에서 TRUE를 지정하거나 range_lookup 인수를 생략하면 Excel에서 대략적인 일치 항목을 찾습니다. FALSE를 range_lookup 인수 로 지정하면 Excel은 정확히 일치하는 항목만 찾습니다.

대략적인 일치 항목 찾기는 수직 또는 수평 조회 테이블의 첫 번째 열이나 행에서 숫자 항목(텍스트가 아닌)을 조회하는 경우에만 관련됩니다. Excel이 이 조회 열 또는 행에서 정확히 일치하는 항목을 찾지 못하면 lookup_value 인수를 초과하지 않는 다음으로 높은 값을 찾은 다음 col_index_num 또는 row_index_num 인수로 지정된 열이나 행의 값을 반환합니다 .

VLOOKUP 및 HLOOKUP 함수를 사용할 때 조회 열 또는 행(즉, 수직 조회 테이블의 맨 왼쪽 열 또는 수평 조회 테이블의 맨 위 행)에 있는 텍스트 또는 숫자 항목은 고유해야 합니다. 또한 이러한 항목은 오름차순으로 정렬하거나 정렬해야 합니다. 즉, 텍스트 항목의 경우 알파벳 순서, 숫자 항목의 경우 가장 낮은 순서에서 가장 높은 순서입니다.

그림은 VLOOKUP 함수를 사용하여 수표의 세전 합계에 따라 팁 테이블에서 15% 또는 20% 팁을 반환하는 예를 보여줍니다. F3 셀에는 VLOOKUP 함수가 포함되어 있습니다.

=VLOOKUP(Pretax_Total,Tip_Table,IF(Tip_Percentage=0.15,2,3)) 

이 수식은 F1 셀의 팁 백분율과 F2 셀의 수표에 대한 세전 금액을 기준으로 팁 금액을 반환합니다.

VLOOKUP 함수를 사용하여 조회 테이블에서 추가할 팁의 양을 반환합니다.

이 팁 테이블을 사용하려면 F1 셀(Tip_Percentage)에 팁 비율(15% 또는 20%)을 입력하고 셀 F2(Pretax_Total)에 세전 수표 금액을 입력합니다. 그런 다음 Excel은 A2:C101 셀 범위를 포함하고 Tip_Table이라는 이름의 조회 테이블의 첫 번째 열에 있는 Pretax_Total 셀에 입력한 값을 조회합니다.

그런 다음 Excel은 일치하는 항목을 찾을 때까지 Tip_Table의 첫 번째 열에 있는 값을 아래로 이동합니다. 그러면 프로그램은 VLOOKUP 함수 의 col_index_num 인수를 사용 하여 테이블의 해당 행에서 셀 F3으로 반환할 팁 금액을 결정합니다. Excel에서 Pretax_Total 셀에 입력한 값(이 예에서는 $16.50)이 Tip_Table의 첫 번째 열에 있는 값 중 하나와 정확히 일치하지 않는다는 것을 발견하면 프로그램은 초과하는 첫 번째 값을 만날 때까지 비교 범위를 계속 검색합니다. 세전 총액(이 예에서는 A19 셀의 17.00)입니다. 그런 다음 Excel은 테이블의 이전 행으로 다시 이동 하고 VLOOKUP 함수 의 col_index_num 인수 와 일치하는 열의 값을 반환합니다 . (선택적인 range_lookup 때문입니다.인수는 함수에서 생략되었습니다.)

그림의 tip 테이블 예제에서는 IF 함수를 사용하여 F3 셀의 VLOOKUP 함수에 대한 col_index_num 인수 를 결정합니다 . IF 함수는 Tip_Percentage(F1 셀)에 입력한 백분율을 0.15와 일치시켜 팁 테이블에 사용할 열 번호를 결정합니다. 일치하는 경우 함수는 col_index_num 인수 로 2를 반환 하고 VLOOKUP 함수는 Tip_Table 범위의 두 번째 열(15% 열 B)의 값을 반환합니다. 그렇지 않으면 IF 함수는 col_index_num 인수 로 3을 반환 하고 VLOOKUP 함수는 Tip_Table 범위의 세 번째 열(20% 열 C)의 값을 반환합니다.

다음 그림은 HLOOKUP 함수를 사용하여 별도의 가격 조회 테이블에 저장된 각 베이커리 항목의 가격을 조회한 다음 해당 가격을 Daily Sales 목록의 Price/Doz 열로 반환하는 예를 보여줍니다. 셀 F3에는 HLOOKUP 함수가 있는 원래 수식이 포함되어 있으며 F열 아래로 복사됩니다.

HLOOKUP 함수를 사용하여 조회 테이블에서 베이커리 항목의 가격을 반환합니다.

=HLOOKUP(항목,가격표,2,FALSE)

이 HLOOKUP 함수에서 C3:C62 범위의 항목 열에 지정되는 범위 이름 Item은 lookup_value 인수 로 정의되고 셀 범위 I1:M2에 지정되는 셀 범위 이름 Price 테이블은 table_array 인수입니다. row_index_num의 사용자가 Excel에서 가격 조회 테이블의 두 번째 행에서 가격을 반환 할 때문에 인수는 2이며, 옵션 range_lookup의 일일 판매 목록의 항목 이름이 가격 조회 테이블에 정확히 항목의 이름과 일치해야하기 때문에 인수는 FALSE입니다 .

HLOOKUP 함수가 가격 테이블 범위를 사용하여 Daily Sales 목록의 각 베이커리 제품 항목에 대해 12개당 가격을 입력하도록 하면 목록의 판매를 업데이트하는 것이 매우 간단해집니다. 이 범위에서 Price/Doz 비용을 변경하기만 하면 HLOOKUP 기능이 항목이 판매되는 곳마다 Daily Sales 목록에서 새 가격을 즉시 업데이트합니다.