VLOOKUP 함수 파헤치기

VLOOKUP 함수 파헤치기

이번 포스팅에서는 실무에 가장 많이 사용하는 함수 중 하나인 VLOOKUP 함수에 대해 알아보도록 하겠습니다.

 

함수의 역할과 구조를 살펴보면, VLOOKUP 함수는 매칭 값을 이용하여 테이블(표) 형식의 데이터에서 원하는 값을 추출하는 함수입니다.

 

함수의 구조는 아래와 같이 확인할 수 있습니다.

 

VLOOKUP(매칭하는 값,데이터 범위,가져오려는 데이터가 속한 열 번호,일치 정확도)

VLOOKUP 함수 마법사

매칭 하는 값은 중복되지 않는 값을 사용해야 정확한 데이터를 찾을 수 있으며, 일치 정확도는 지정한 매칭 값과 데이터의 에서 매칭 값과 대응되는 값이 완전히 일치하는지, 비슷하게 일치하는지 여부를 나타내는 것으로 완전 일치는 FALSE(숫자 0으로 대체 가능)를 입력하고 비슷하게 일치하는 조건은 TRUE(숫자 1로 대체 가능)를 입력합니다.

 

※ VLOOKUP 함수 사용 시 주의해야 될 사항으로 테이블(표)의 가장 좌측 열에는 매칭 하는 값이 포함된 데이터가 있어야 됩니다.

해당 데이터와 비교하여 테이블의 우측에 나열된 데이터를 추출하는 방식이므로 항상 테이블의 가장 좌측에는 매칭 하는 값이 입력된 데이터가 있어야 됩니다.

 

 

1. VLOOKUP 함수 설명 및 예제(1)

 

VLOOKUP 함수는 첫 번째 인수인 매칭 하려는 값의 데이터가 불러오려는 데이터가 포함된 테이블의 가장 좌측에 위치해야 되며, 해당 값을 매칭 하여 테이블에서 우측으로 몇 번째 열 만큼 떨어져 있는지를 확인하여 값을 불러오는 함수입니다.

 

예제를 통해 좀 더 쉽게 설명하면,

예제1

구매자에 대한 정보가 위와 같이 테이블(표)로 나타나 있습니다. 각 구매자 별로 중복되지 않는 고유 번호가 부여되어 있으며, 해당 고유번호를 매칭 하여 이름과 구매일, 물품번호, 전화번호를 우측 표에 불러올 수 있습니다.

 

만약 VLOOKUP 함수를 사용하지 않는다면 고유번호 'A881273'에 해당하는 고객의 정보는 어떻게 불러올 수 있을까요? 가장 먼저 생각할 수 있는 방법은 필터를 적용한 후 고유번호에 'A881273'을 입력한 후 확인을 눌러 해당 고객만 나오도록 필터링하고 표에 정보를 붙여 넣을 수 있을 것입니다.

 

하지만 찾아야 되는 대상이 많을 경우 일일이 필터링을 하는 작업은 비효율적이므로 함수를 이용하는 방법을 익힐 필요가 있습니다.

 

VLOOKUP 함수를 이용하여 고유번호에 매칭 되는 사람을 표에서 찾아 이름과 구매일, 물품번호, 전화번호를 불러오는 수식을 작성해보도록 하겠습니다.

 

우선 이름을 불러오기 위해 H셀에 수식을 입력할 때 매칭 값은 G열의 고유번호를 선택한 후 A3부터 E27까지 범위를 설정하고, 테이블에서 이름이 입력된 열은 2번째 열이므로 2번째 열에 있는 값을 불러오고 고유번호와 정확히 일치하는 값을 가져와야 되므로 마지막 인수는 FALSE(숫자 0으로 대체 가능)를 입력해 줍니다.

 

이를 풀어쓰면 VLOOKUP($G3,$A$3:$E$27,2,0)과 같이 수식을 작성할 수 있으며, 자세한 풀이는 아래와 같습니다.

예제1 풀이

H, I, J, K열 3번째 행의 셀에 각 수식을 입력한 후 아래로 드래그하면 되며, 각각의 정보는 원래 데이터에서 해당하는 열 번호가 다르므로 수식을 각각 입력해야 됩니다.

 

※ 데이터의 목록 값과 입력하고자 하는 표의 목록 값이 같은 경우 일일이 열 번호를 따로 입력하며 수식을 작성하지 않아도 되는 방법은 아래에서 설명하도록 하겠습니다. 

 

여기서 주의해야 될 사항으로 전체 데이터 표의 범위는 아래로 드래그해도 변하면 안 되므로 행과 열을 모두 고정해야 됩니다.

 

 

2. VLOOKUP 함수 예제(2)

 

예제2

이번 예제는 테이블에서 두 번째 열에 '1' 또는 '2'로 구분 값이 주어진 상황에서 구분 값에 따라 '이름' 또는 '물품번호'를 불러오는 예제입니다.

 

조건이 추가되어 다소 복잡하게 느껴질 수 있으나 조금 깊이 생각하고 지금까지 배운 수식을 활용하면 문제를 해결할 수 있습니다.

 

우선 구분 값이 '1'인지 '2'인지에 따라 구하는 결과가 달라지므로 고유번호에 해당하는 구분 값을 확인하는 과정을 먼저 거쳐야 됩니다.

그 후 구분 값에 따라 추출하는 값을 다르게 가져오는 결과 수식을 작성하면 됩니다.

 

조건을 부여해야 되므로 IF 함수를 활용할 수 있고 이에 따른 결과를 추출하는 함수는 VLOOKUP을 사용하면 됩니다.

 

정리한 내용을 토대로 함수를 구성해보면,

IF(고유번호 매칭 구분값=1,VLOOKUP(고유번호,테이블 범위,이름이 있는 열 번호,0),VLOOKUP(고유번호,테이블 범위,물품번호가 있는 열 번호,0))<로 정리할 수 있으며, 여기서 IF 함수의 조건 부분인 '고유번호 매칭 구분값'이 1인지 여부 확인을 위해 VLOOKUP 함수를 이용하여 VLOOKUP(고유번호,테이블 범위,구분값이 있는 열 번호,0)=1로 적용합니다.

 

이를 풀어보면 아래와 같이 답이 나오는 것을 확인할 수 있습니다.

예제2 풀이

적용된 수식은 IF(VLOOKUP(H3,$A$3:$B:27,2,0)=1,VLOOKUP(H3,$A$3:$B:27,3,0),VLOOKUP(H3,$A$3:$B:27,5,0))입니다.

이는 VLOOKUP(H3,$A$3:$B:27,2,0)를 통해 고유번호에 해당하는 구분 값이 '1'인지 여부를 확인하고 '1'일 경우 VLOOKUP(H3,$A$3:$B:27,3,0) </b식을 이용하여 테이블의 3번째 열인 이름을 반환하며, IF 함수의 마지막 인수인 조건에 맞지 않을 경우, 즉 '1'이 아닐 경우 주어진 예제에서는 '2'밖에 나올 수 없으므로 이때에는 VLOOKUP(H3,$A$3:$B:27,5,0) 함수를 통해 5번째 열의 물품 번호를 반환하게 됩니다.

 

다소 복잡하게 느껴질 수 있으나 어떻게 수식을 작성할지 확인하고 지금까지 배운 함수를 응용한다면 문제를 쉽게 해결할 수 있습니다.

 

 

3. VLOOKUP 함수의 열 번호 자동 변경하기

 

데이터의 목록 값과 입력하고자 하는 표의 목록 값이 같은 경우 열 번호를 따로 입력하며 수식을 작성하지 않아도 되는 방법을 설명하도록 하겠습니다.

 

해당 방법의 핵심은 COLUMN 함수를 사용하는 것으로 함수의 구조는 COLUMN함수의 인수로 입력한 셀의 열 번호를 반환하는 함수입니다.

 

만약 COLUMN(A3)로 입력한다면 값은 A열의 열 번호 '1'을 반환하게 됩니다. A열은 무조건 열 번호가 1번 이므로 COLUMN함수의 인수로 A10, A150, A100을 넣어도 값은 '1'이 나옵니다.

 

B1, B5, B100과 같이 B열에 해당하는 값을 넣으면? 값은 '2'가 됩니다. 이렇게 A열의 반환 값이 '1'이며, 옆으로 이동하면 해당 열에 맞는 열 번호가 반환됩니다.

COLUMN함수 예

COLUMN 함수를 이용하여 예제 1번 문제를 수식 입력 후 드래그하는 방식으로 풀어보면 아래와 같습니다.

풀이

H3 셀에 입력한 수식은 VLOOKUP($G3,$A$3:$E$27,COLUMN(B$1),0)입니다.

 

수식을 오른쪽으로 드래그하면 열 부분인 B열이 C, D열로 변해야 되므로 COLUMN함수 안의 B열은 고정하지 않았고 행은 변하지 않도록 고정하여 $1로 넣어 수식을 완성하였습니다.(위의 상황에서 행을 고정하지 않아도 됩니다.)

 

원리는 열 번호가 옆으로 이동할 때마다 2, 3, 4, 5로 일정하게 변하므로 드래그를 통해 수식을 이동시켜 열 번호가 일정하게 변하도록 한 것입니다.

 

다음 포스팅에서는 HLOOKUP 함수와 LOOKUP 함수에 대해 알아보는 시간을 갖도록 하겠습니다.