Vlookup 함수(1) - 구문, 정확한 값, 유사값 찾기

1. 의미

원하는 값을 세로 방향으로 찾아, 찾은 줄에서 오른쪽으로 지정된 번째의 값을 가져오는 함수입니다.
오른쪽에 있는 값을 가져오므로 왼쪽에 있는 값을 가져올 수 없다는 점 주의해야 합니다.

2. 구문

(한글)

=VLOOKUP(조회하려는 항목, 찾고자 하는 위치, 반환할 값이 포함된 범위의 열 번호, 대략적 또는 정확히 일치 반환 - 1/TRUE 또는 0/FALSE로 표시)

(영어)

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

lookup_value : 찾을 값

table_array : 검색할 대상인 표 또는 범위, 배열. 찾을 값이 설정된 범위의 첫 번째 열에 있어야 함. 다시 말해 찾을 값이 B열에 있다면 B열부터 범위를 지정해야 함

col_index_num : 찾고자 하는 열의 순번 또는 오른쪽으로 이동할 열의 개수.
첫 번째가 1이고, 오른쪽으로 이동할수록 1씩 증가함. 다시 말해 B열부터 D열을 table_array라면 B열이 1, D열은 3이 됨

range_lookup :
- 정확한 값 반환 : 0 또는 False.
- 근사(유사) 값 반환(default, 기본 값) : 1 또는 True. Default이므로 생략하면 유사한 값을 찾는데 찾을 범위의 첫 번째 열이 오름차순으로 정렬되어 있어야 근사(유사) 값을 찾을 수 있음

3. 사용 예

 

vlookup.xlsx

0.01MB

 

 

가. 판매일에 해당하는 지점명, 판매금액 찾기

아래 표에서 판매일에 해당하는 지점명과 판매금액을 추출해 보겠습니다.

찾을 값은 F2셀부터 F4셀이고,

 

찾을 범위는 판매일이 B열에 있고, 지점명은 C열, 판매금액은 D열에 있으므로, B2셀부터 D16셀까지가 되는데, 아래로 복사할 때 범위가 변하면 안 되므로 절대 참조형식으로 지정해야 합니다.

열의 순번은 판매일이 1, 지점명이 2, 판매금액은 3이 됩니다.

또한 정확한 값을 찾을 것이므로 0이라고 입력합니다.

따라서, 이를 모두 결합하면 G2셀의 수식은 =vlookup(f2, $b$2:$d$16, 2, 0)이 됩니다.
위 수식을 복사한 다음 G2셀에 붙여넣어도 됩니다.

 

또는 수식을 입력할 때 =vl까지 입력하면 vlookup이 표시되므로 탭키를 누르고,

범위는 B2셀을 선택한 다음 D16셀까지 끌어도 되고,

B2셀을 클릭한 다음 Shift 키를 누른 상태에서 Ctrl + →키를 눌러 오른쪽 끝으로 이동하고, 다시 Ctrl + ↓를 눌러 맨 아래로 이동해도 됩니다.

그리고, 절대 참조 형식으로 지정하기 위해 F4키를 누릅니다.

그리고, ,2, 0)를 입력해서 수식을 완성합니다.

그러면 값이 지점명 A가 반환됩니다.

 

(수식 수정)

찾을 값 F2셀이 오른쪽으로 수식을 복사할 때 G로 바뀌게 되므로 F열을 고정해야 하며, 열은 아래로 내려갈 때 바뀌어야 하므로 고정하면 안 되므로 $F2식으로 혼합 참조 형식으로 바꿔야 합니다.

$F2는 $기호를 F왼쪽에 직접 입력해도 되고,
수식 줄에서 F2를 마우스로 클릭해서 F2 전체를 선택하거나 F2 사이에 커서를 넣은 다음
F4키를 누르면 $F$2 -> F$2 -> $F2 -> F2로 바뀌므로 해당 참조 형식이 나왔을 때 멈춰도 됩니다.

이제 G2셀의 채우기 핸들을 오른쪽으로 끕니다.
값이 A로 같은데 열 순번이 2라서 그러므로 h2셀을 클릭한 다음 2를 3으로 수정하고 엔터 키를 누릅니다.

그러면 판매금액 516000원이 반환됩니다.

이제 G2셀부터 H2셀까지 마우스로 끌어서 범위를 선택한 다음 H2셀의 채우기 핸들을 더블 클릭합니다.
그러면 3행과 4행의 판매일에 해당하는 지점명과 판매금액이 구해집니다.

판매 금액에 쉼표를 넣으려면 H열 또는 H2셀부터 H4셀까지 선택한 다음 홈 탭 > 표시 형식 그룹 에서 ,(쉼표)를 클릭하면 됩니다.

 

나. 지점명 해당하는 판매금액 찾기

아래와 같이 F8셀부터 F10셀까지 A, B, C 지점명을 입력하고 그 옆에 판매금액을 찾아서 입력하는 것을 해보겠습니다.

찾을 범위는 지점명이 첫 번째 열이 되어야 하므로
C2셀에서 D16셀까지인데, 이동시 셀 주소가 변경되면 안 되므로 절대참조 형식으로 지정해야 합니다.

그리고, 판매금액은 지점명 오른쪽에 있으므로 열 순번은 2가 됩니다.

또한 이번에는 찾을 값을 오른쪽으로 이동하지 않으므로 $F8식으로 F열을 고정할 필요가 없습니다.

따라서, 수식은 =vlookup(f8, $c$2:$d$16, 2, 0)가 됩니다.
그러면 G8셀에 516000이 구해집니다.

516000이란 지점명이 A인 것의 첫 번째 값입니다. A지점이 여럿 있더라도 첫 번째 값만 찾아주는 것이 Vlookup 함수의 단점입니다.

G8셀의 채우기 핸들을 더블 클릭해서 G10셀까지 수식을 복사합니다.
그러면, B지점에 해당하는 첫 번째 값 975000, C지점에 해당하는 값 942000이 구해집니다.

F9셀을 A로 바꿔도 지점 A에 해당하는 첫번째 값이 반환되기 때문에, 두 번째 값 551000이 아닌 516000이 구해집니다.

이것은 Aggregate 함수를 이용해 구할 수 있는데 따로 다루도록 하겠습니다.

다. 근사(유사) 값 찾기

판매금액이 516000과 975000은 정확한 값인데, 600000과 500000은 판매금액에 없습니다.
따라서, 네 번째 인수로 1 또는 True을 입력해서 근사한 값을 구해보겠습니다.

찾을 범위는 판매금액이 있는 D2:D16인데 절대참조 형식으로 지정해도 되며,

열 순번은 하나뿐이 없으므로 1이 됩니다.

따라서, G13셀의 수식은 =vlookup(f13,$D$2:$D$16,1,1)이 됩니다.
$D$2:$D$16는 d2:d16까지 입력한 후 F4키를 눌러도 되고,
마우스로 범위를 선택한 후 F4키를 눌러도 됩니다.
그런데, 만약 D16에만 $기호가 들어가면 D2를 클릭한 다음 F4키를 눌러야 합니다.

근사한 값을 찾는 것이기 때문에 정확한 값이 있으면 정확한 값을 반환합니다. 그러나, 두 번째는 정확한 값이 950000이 잇는데 292000이 반환되고, 세 번째는 근사한 값으로 516000을 반환하는데, 네 번째는 #N/A(Not Available, 찾을 수 없음)이라고 에러가 표시됩니다.

(근사한 값 찾기를 하려면 먼저 정렬되어 있어야 함)

(오름차순 정렬)
판매금액이 정렬되지 않아서 네 번째 값이 #N/A라고 값이 나온 것이므로 판매금액 순으로 정렬해 보겠습니다.

D2셀 등 D열에 커서를 넣은 후 데이터 > 정렬 및 필터그룹에서 오름차순 정렬 명령을 누릅니다.

그러면 자동으로 G13셀부터 G16셀의 값이 수정되는데, 첫 번째와 두 번째는 정확한 값이 반환되고,
세 번째와 네 번째는 찾으려고 하는 값보다 작은 값이 반환됩니다.

(내림차순 정렬)
데이터 > 정렬 및 필터그룹에서 내림차순 정렬 명령을 누르니 이상한 값 또는 #N/A값이 반환됩니다.

따라서, 오름차순으로 정렬되어야 합니다.

vlookup(중간).xlsx

0.01MB