엑셀(excel) 함수 TIP 24 - (VLOOKUP, HLOOKUP함수로 원하는 값 추출하기)

,,., 2018. 9. 13. 07:50

엑셀(excel) 함수 TIP 24 - (VLOOKUP, HLOOKUP함수로 원하는 값 추출하기)

VLOOKUP 함수를 사용하면 셀의 첫 번째 열을 검색한 다음 해당 범위의 같은 행에 있는 셀 값을 반환할 수 있습니다. 즉 아래와 같이 A2:F6의 범위에 학생의 목록이 있고 첫째 열에 학생의 고유번호(ID)가 있을 때 VLOOKUP 함수를 이용하여 학생의 이름이나 각각의 점수를 반환할 수 있습니다.

저 같은 경우에도 직장에서 VLOOKUP함수를 사용해서 중복값찾기를 많이 하는데 예를 들어 수천개행이 있는 원본데이터에서 찾고자 하는 값(중복데이터 값)이 10개정도 있다고 하나하나를 ctrl + f(찾기)를 한다면 찾을 수 있지만 엄청 시간이 소요될 것입니다. 이 때 VLOOKUP이나 HLOOKUP함수를 사용해서 중복값(데이터)를 찾는다면 불과 몇분안에 해결할 수 있습니다.

● VLOOKUP함수 구문

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

"표(table_array)의 첫행에서 지정함 값(lookup_value)을 찾아 지정한 열의(col_index_num) 같은 행에 있는 값으로 반환하라"는 의미입니다.

lookup_value 필수 요소로서 표 또는 범위의 첫 번째 열에서 찾을 값.
table_array 데이터가 들어 있는 셀 범위를 나타냄. 범위에 대한 참조(예: A2:D8) 또는 범위 이름을 사용할 수 있음.
col_index_num 반환해야 하는 값이 있는 table_array 인수의 열 번호 col_index_num 인수가 1이면 table_array의 첫 번째 열 값이 반환되고, 2이면 table_array의 두 번째 열 값이 반환
[range_lookup] FALSE(0) : 정확한 값만, TRUE(1) : 정확한 값이나 근사값

중요사항 range_lookup이 TRUE이거나 생략되면 table_array의 첫 번째 열 값을 오름차순으로 정렬해야 합니다. 그렇지 않으면 VLOOKUP 함수를 실행하여 올바른 결과를 얻을 수 없습니다.range_lookup이 FALSE이면 table_array의 첫 번째 열 값을 정렬할 필요가 없습니다.

● VLOOKUP 함수 사용예 -1

학생의 ID 4에 해당하는 이름을 가져오려면 =VLOOKUP(4, A2:F6, 2, FALSE) 이 수식은 A2:F6범위의 첫 번째 열에서 값 2를 검색한 다음 해당 범위의 두 번째 열 및 조회 값과 같은 행의 값을 반환합니다("김충만")

=VLOOKUP(4, A2:F6, 2, FALSE) 의 값을 반환하면 하단과 같은 결과값이 나타납니다. LOOKUP이란 뜻은 찾아보다란 뜻이고 V는 vertical의 약자로 세로란 뜻입니다. false대신 0을 입력해도 되고 true대신 1을 입력해도 됩니다.

● VLOOKUP 함수 사용예 -2

하단과 같이 2개의 데이터가 있습니다. 좌측에는 제품별 제고목록이 우측에는 제품별 생산공장이 있습니다. 좌측의 빈 공란을 우측의 자료를 이용하여 구할 수 있습니다. 이때 이용할 수 있는 함수가 VLOOKUP함수 입니다. 만약 이 함수가 없다면 하나하나 복사해서 붙여넣기를 하던지 보고 입력을 해야 할 것입니다. 엑셀이란 생각할 수록 참 좋은 프로그램입니다.

하단의 자료에서 참조되는 범위가 하단방향으로 행을 기준으로 정렬이 되어 있기 때문에 VLOOKUP함수를 이용합니다.

[구하고자 하는 셀 선택(B3) - 수식 - 찾기/참조영역 - VLOOLUP]

▶ 함수인수창에서 작업

[lookup_value(A3) : 값을 구 하는 테이블에서 첫열에서 찾고자 하는 값]

[table_array : 참조하는 테이블의 전체범위로서 절대참조의 값/(F2:H7)을선택 후 절대참조로 변경(F4기능키)]

[col_index_num : 참조하는 테이블에서 구하고자 하는[제고목록]하는 열의 번호(2)]

[range_lookup : false로 정확한 값을 찾고자 함]

위와 같이 함수인수창에서 작업을 하고 [확인]을 하면 [C3]에 [창원]입력이 됨 해당셀 선택 후 채우기 핸들[마우스드래그]

사용된 VLOOKUP함수 [=VLOOKUP(A3,$F$3:$H$7,2,FALSE)]

● HLOOKUP 함수 사용예 -2

하단과 같이 좌측에는 제품별 제고목록, 우측에는 제품번호별 제품상태가 나타나 있습니다. 또한 [제품번호의 마지막 글자]를 제품상태를 나타내는 [P,Q,R]로 되어 있습니다 이때 [제품번호별 제품상태]를 가로방향(열)로 구성되어 있기 때문에 HLOOKUP함수를 사용하여 해당 값을 구합니다. 다만 여기서는 [제품번호]의 [마지막 글자]를 참조해야 하기 때문에 함수 중

LIGHT함수를 HLOOKUP함수와 중첨 사용해야 합니다.