엑셀(Excel)함수, 찾는값과 연관되 값을 가져오는 VLOOKUP, HKOOJUP함수

Excel VLOOKUP, HLOOKUP함수

엑셀문서를 작성하다 보면 인사업무나 회계업무등에서 가장많이 사용하는 함수중 하나인

대표적인 엑셀함수라고 볼 수 있겠습니다.

데이터의 목록의 첫 번째 열에서 찾는값과 일치하는 데이터를 찾은후 같은 행에서 연관된 지정열

(VLOOPUP) 또는 지정 행번호(HLOOKUP) 위치에 있는 데이터를 가져오는 함수입니다

즉 어떠한 기준표에 의해 기준표의 대표항목을 찾아 그에 연관된 데이터를 가져오는 함수이지요

함수형식 : =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

              =HLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

               > lookup_value : 찾을값(데이터 범위의 첫번째 열(행)에서)

               > table_array : 찾을 데이터 범위(복사를 위해 절대번지지정함 F4키)

               > col_index_num : 해당열 범위(가져올 결과값이 있는 열(행)번호)

               > range_lookup : 찾을 방법(정확히 일치false, 근사값일치true)

vlookup의 첫번째 문자 V는 vertical 수직을 뜻하므로 데이터 목록이 수직 방향인 경우 사용

hlookup의 경우 H는 horizontal 데이터 목록이 수평방향으로 긴 경우에 사용합니다.

 

아래 출장비 지급 명세서를 예로 들어 볼께요

"출장지별 지급기준표"에서 출장장소에 해당하는  교통비, 숙박비를 찾아 각항에 맞게표시하고 

"직급별 출장 수당지급표"에서 직급별 수당을 찾아 해당직급에 맞게 수당을 표시하여 줍니다.

이름이 하나인 과장이 춘천으로 출장을 가면 교통비 D3셀에 =VLOOKUP(C3,$A$14:$b$16,2,FALSE)

를 작성하면 "출장비별 지급기준표"(A14:B16)에서 출장지 "춘천"(C3셀)을 찾아 지정한 데이터 범위인

A14:B16 에서 2번째열의 값을 정확히 일치하게(FALSE) 찾아 가져와 표시하여 줍니다.

범위를 지정할때  수식복사를 위해 절대번지 형태로 지정합니다(F4키) A14:B16=>F4=>$A$14:$b$16

 

 숙박비를 찾아 해당하는 데이터를 가져오는 방법도 위방법과 동일합니다. 하나과장의 출장지인 춘천

을 찾기위해 "=VLOOKUP(춘천C3셀, 찾을범위A14:C16, 범위에서3번째열,정확히일치FALSE)" 이같은

방법으로 하면 "출장지별 지급기준표"에서 춘천에 해당하는 숙박비를 가져와 표시하여 줍니다.

 

직급별 출장 수당을 구해봅니다. "직급별 출장 수당 지급표" 를 보시면 가로로 길게 데이터가 작성되

있는데요 이땐 HOOKUP 함수를 사용하면 됩니다.

이름이 하나 과장인 출장직급수당은(F3셀) =HLOOKUP(직급과장(B3셀), 데이터범위(A19:E20),

범위에서2번째행, 정확히일치FALSE) 방법으로 직급별 수당을 찾아 표시하게 됩니다.

또 주의해야 할 점은 만약 찾는 값이 2개일경우엔 값이 맞지않게 되는데요 이럴땐 COUNTIF함수로

1명보다 많은지 IF함수로 조건을 걸어 "error"표시를 하여 사용할 수도 있습니다.

아래 예시에서 누리가 2명이 있습니다 근데 누리의 성적을 Vlookup함수를 사용하여 보니 80점을

표시합니다 3번째에 누리 60점이 또 있으니 누리를 80이라고 표시한건 맞지 않는 값이 되네요

 위에 COUNTIF 함수를 사용하여 찾는 값이 몇개인지를 찾아보면 데이터범위 B2:B9 에서

B9셀의 "누리" 와 같은 값이 몇개인지를 표시하여 준 것입니다 같은 데이터가 2개 이지요

 이때 한가지 방법으로 IF함수로 찾는데이터가 몇개인지(COUNTIF) 1개면 참:Vlookup실행, 1개가

아니면 "error"표시 하도록 하는 것도 한가지 방법이 되겠지요

 데이터 목록에서 원하는 값을 찾아 연관된 값을 표시해 주는 VLOOKUP, HLOOKUP 함수와

찾는 데이터가 2개이상일 경우 IF함수, COUNTIF함수로 조건을 걸어 목록을 찾을 것인지 아니면

에러메세지를 표시할 것인지를 알아보았습니다.