VLOOKUP 정복하기

엑셀을 사용하면서 엑셀의 많은 기능중에서 기능을 조금 사용한다 싶으면 자주 등장하는 함수가 바로 VLOOKUP입니다. 업무를 효율적으로 수행하는데 없어서는 안되는 필수 함수입니다. VLOOKUP의 설명을 위해 사용할 예제는 다음과 같습니다.

지역코드-상품코드-수량으로 작성된 표에서 지역코드에 대한 지역명을 찾아서 열을 추가하고 싶은 필요가 있다면 이를 해결할 수 있는 방법은 지역코드에 해당하는 지역이름이 나열된 목록(list)만 존재한다면 VLOOKUP 함수로 간단하게 해결할 수 있습니다. 

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

함수의 인수들은 위와 같습니다. range_lookup은 옵션 항목으로 입력하지 않으면 true로 간주합니다. 의미는 true인 경우에는 정확하게 일치하는 것을 찾는 것이고 false면 정확하게 일치 하지 않아도 가장 비슷한 것을 찾는다는 의미입니다.  일단 따라하기로 직접 작성해 보죠. D2 셀에서 =VLOOKUP(을 입력해서 수식 입력을 시작합니다.

함수의 수식 입력을 시작하기 위해서 괄호를 열면 위의 그림과 같이 첫번째 인수 lookup_value를 입력하라고 인수를 굵은 글씨로 표시해 줍니다. lookup_value는 다른 표에서 찾을 기준값으로 예제에서는 지역코드를 찾을 것이므로 A2을 클릭합니다.

A2 셀을 클릭하면 위의 그림과 같이 수식에 셀주소가 입력되므로 콤마(,)를 입력합니다. 콤마(,)를 입력하면 다음 인수 table_array를 입력하라고 굵은 글씨로 표시해 줍니다. table_array는 기준값을 찾아서 원하는 값을 추출할 표의 범위로 같은 시트에 있어도 되고, 다른시트나 다른 파일에 있는 표도 입력할 수 있습니다. 중요한 점은 기준 테이블(table_array)의 첫번째 열을 수직으로(위에서 아래로) 검색하는 것이므로 테이블의 첫번째 열을 기준으로 표가 정렬되어 있어야 하고 첫번째 열의 값중에서 첫번째 인수 lookup_value와 같은 값이 있어야 합니다. 여기서 찾지 못하면 vlookup 함수의 결과는 "N/A"가 표시될 것입니다. 대상 테이블(table_array)이 준비되었으면 마우스로 좌상단에서 우하단으로 끌어다 놓기 방식으로 테이블의 범위를 선택합니다.

예제에서는 H2에서 I4까지를 선택했습니다. 문제는 이런 VLOOKUP을 다른 셀에도 적용하고 싶다면 테이블 범위를 선택한 주소를 F4 단축키를 사용하여 시작셀:종료셀 범위를 모두 절대 주소($가 붙은 셀주소) 형태로 바꾸어야 합니다. 이렇게 하지 않으면 D2의 수식을 D3에 복사하면 테이블 범위도 바뀌어서 엉뚱한 결과를 가져오기 때문입니다. 테이블 범위 선택이 끝나면 콤마(,)를 입력해서 다음 인수를 입력합니다.

마지막 인수 col_index_num은 테이블에서 기준값과 일치하는 행을 찾았다면 어떤 값을 추출할 것인지를 지정하는 것으로 첫번째 열은 1입니다. 예제에서는 일치하는 지역코드를 찾으면 같은 행의 지역이름을 추출할 것이므로 두번째 열을 의미하는 2를 입력하고 괄호를 닫고 엔터를 입력해서 수식 입력을 끝냈습니다.

D2의 VLOOKUP 수식이 정상적으로 가져온 것을 확인했으면 다른 셀로 복사합니다.

■ 기준열이 여러개일때 VLOOKUP 사용하기

위의 예제에서는 지역코드 하나를 대상 테이블의 첫 컬럼(지역 코드 열)에서 수직으로(위에서 아래로) 검색하여 찾아진 행의 두번째 열인 지역 이름을 추출했으나 지역코드와 상품코드가 모두 일치하는, 즉 여러개의 열을 검색 기준으로 사용하려면 기본적인 방법으로는 방법이 없습니다. 

이번 예제는 지역코드와 상품코드가 모두 일치하는 행을 찾아서 수량을 추출하는 예제입니다. H와 I열에 찾을 기준 값을 입력하고 J열의 값은 수식으로 A2:C6을 대상 테이블에서 수량을 추출하는 예제입니다.

CHOOSE, INDEX, MATCH등의 함수를 사용해서 수행하는 방법이 있지만 복잡하고 정확성에 의문이 있습니다. 여기에서 제시하는 방법은 약간 번거롭기는 하지만 대상 테이블에 다중 열 검색에 맞도록 임시 열을 추가해서 VLOOKUP을 사용하는 것입니다.

대상 테이블 앞에 VLOOKUP 검색의 기준열로 사용할 임시 열을 추가하는데 문자열 접합 연산자(&)를 이용해서 지역코드와 상품코드를 붙이는 수식을 작성합니다. 또한 임시열을 기준으로 대상 테이블을 정렬합니다. 만약에 원본이 보존되어야 한다면 별도의 시트에 복사해서 작업하거나 다른 이름으로 보관해서 작업을 진행하는 것이 안전하겠지요!

임시열을 추가해서 정렬하는 것으로 대상 테이블이 준비되었다면 위의 그림과 같이 VLOOKUP 수식을 기술합니다. lookup_value에 두 컬럼 값을 붙여서 비교할 것이므로 H2 셀을 클릭한 다음에 &를 입력하고 I2셀을 클릭하면 됩니다. 두번째 인수인 대상 테이블의 범위 설정은 앞선 예제와 같은 방식으로 지정하고 F4 단축키로 절대 주소로 변환합니다. 마지막 인수인 col_index_num은 수량이 대상 테이블 범위에서 네번째 값이므로 4를 입력하면 됩니다.

VLOOKUP 수식을 입력해서 값이 정상적으로 추출되면 다른 셀로도 복사하면 다중 컬럼을 기준값으로 하는 VLOOKUP도 정상적으로 수행할 수 있습니다.