엑셀 VLOOKUP 함수 예제

엑셀 VLOOKUP 함수 예제

 

엑셀에서 사용되는 함수 중 VLOOKUP 함수는 자주 사용되면서도 엑셀 초보자에게는 다소 어려운 함수입니다.

간단한 예제를 통해 VLOOKUP 함수 사용법에 대해 살펴보겠습니다.

 

우선 VLOOKUP 함수의 함수명을 쪼개 보면 Vertical Look Up 을 줄여서 쓴 말로, Vertical은 수직의 의미이고, Look Up은 찾는다는 의미입니다.

즉 수직으로 나열된 데이터에서 원하는 값을 찾겠다는 의미를 줄여서 VLOOKUP 함수라고 지칭된 것입니다.

 

이와 유사한 함수로 HLOOKUP 함수가 있습니다. HLOOKUP 에서 H는 Horizontal 을 뜻하며, 수평이라는 의미입니다.

그러므로 HLOOKUP은 Horizontal Look Up의 줄임말이며, 수평으로 나열된 데이터에서 원하는 값을 찾겠다는 의미입니다.

 

VLOOKUP 함수를 제대로 활용하기 위해서는 상대참조, 절대참조, 혼합참조 개념이 제대로 정립되어 있어야 하므로, 상대참조, 절대참조, 혼합참조에 대한 개념은 알고 있다는 전제 하에 설명드리도록 하겠습니다.

 

 

위의 그림에서 왼쪽의 표는 고정데이터이고, 오른쪽의 표에는 제품코드를 입력하면 왼쪽의 표에서 데이터를 가져와서 제품명, 단위, 단가, 금액을 자동으로 표시하도록 하기 위한 예제입니다.

 

 

위의 그림처럼 제품코드가 입력된 상태에서 해당 제품명이 자동으로 출력되도록 VLOOKUP 함수를 사용해 보겠습니다.

G4 셀에 커서를 위치 시킨 후 함수마법사 아이콘을 클릭하거나 함수마법사 단축키인 SHIFT+F3키를 눌러서 함수마법사를 띄운 후 VLOOKUP 함수를 선택합니다.

 

 

VLOOKUP 함수를 최근에 사용했다면 "범주 선택"에서 "최근에 사용한 함수"를 선택 후 "함수 선택" 목록에서 VLOOKUP 함수를 선택하면 되고, 그렇지 않은 경우에는 위의 그림처럼 "범주 선택"에서 "모두"를 선택하여 VLOOKUP 함수를 선택해 줍니다.

 

 

우선 VLOOKUP 함수의 인수에 대한 설명을 먼저 드리겠습니다.

 

1번째 인수인 Lookup_value는 표의 첫 열에서 찾으려는 값으로 위의 예제의 경우는 제품코드 100을 기준으로 제품명을 가져오겠다는 의미입니다. 그러므로 위의 그림에서 왼쪽 표의 첫 번째 열에는 제품코드가 나와야 하는 것입니다. 제품코드가 B열이나 C열 등 1번째 열이 아닌 곳에 있으면 VLOOKUP 함수를 사용해서는 원하는 값을 가져올 수 없습니다.

 

2번째 인수인 Table_array는 데이터를 검색하고 추출하려는 표입니다. 위의 그림에서는 왼쪽의 표가 해당됩니다.

 

3번째 인수인 Col_index_num은 2번째 인수에서 지정한 범위 중 1번째 인수가 일치할 경우 추출할 열 번호를 입력합니다.

 

4번째 인수인 Range_lookup은 값을 찾을 때 정확하게 일치하는 것을 찾을 것인지, 아니면 비슷하게 일치하는 것을 찾을 것인지를 선택하는 것으로 정확하게 일치하는 것을 찾을 때는 0 이나 FALSE를 입력하고, 비슷하게 일치하는 것을 찾을 때는 TRUE를 입력하거나 생략해도 되며 0 이 아닌 값을 넣어도 TRUE의 의미를 가지므로 통상 1을 많이 입력합니다.

 

 

1번째 인수에는 표의 첫 열에서 찾으려는 값으로, 제품코드를 기준으로 찾을 것이며, G4 셀의 제품명은 F4 셀에 입력된 제품코드 100을 기준으로 가져올 것이기 때문에 F4 셀을 입력합니다.

 

 

2번째 인수인 Table_array는 데이터를 검색하고 추출하려는 표로, 제품코드를 기준으로 제품명을 가져올 것이기 때문에 A4셀부터 D10셀까지를 범위로 지정해 줍니다. 물론 제품명을 가져올 때는 범위를 A4셀부터 B10셀까지만 지정해도 가능하지만 단위, 단가의 내용을 추출할 때도 동일한 범위를 사용하기 위해 범위를 A4셀부터 D10셀로 지정했습니다.

 

 

2번째 인수에서 A4셀부터 D10셀까지를 범위 지정한 후 참조방식 변경 단축키인 F4키를 눌러서 상대참조 방식인 A4:D10을 절대참조 방식인 $A$4:$D$10으로 변경해 줍니다.

이 때 2번째 인수에서 범위 선택 후 커서를 다른 곳으로 이동했다가 2번째 인수 위치로 이동한 경우 또는 수식을 변경하기 위해 함수마법사를 종료했다가 함수마법사를 다시 들어온 경우에는 F4키를 눌렀을 때 $A$4:$D$10로 지정되지 않을 수도 있습니다. 이 때는 A4:D10라고 입력된 2번째 인수의 내용 전체를 범위 지정 후 F4키를 눌러주면 $A$4:$D$10로 변경됩니다.

 

위의 설명처럼 A4:D10 범위를 $A$4:$D$10로 변경한 이유는 제품명을 추출 후 채우기핸들을 이용해서 나머지 셀에 대해서도 값을 구할 때 참조범위가 변경되지 않고 동일한 범위가 지정되도록 하기 위해 절대참조로 변경해 주는 것입니다.

 

F4 키를 눌러서 절대참조로 변경하는 이유에 대해 잘 모르겠다고 느껴지는 분은 엑셀 상대참조, 절대참조, 혼합참조 예제 설명을 먼저 읽어보신 후 다음 과정을 진행하시기 바랍니다.

 

 

위의 예제에서 범위를 지정할 때 A3:D10셀을 선택하지 않고, A4:D10셀을 선택하는 이유는,

우리가 찾고자 하는 데이터는 제품코드 100부터 700까지의 값을 기준으로 제품명, 단위, 단가를 가져올 것이며, "제품코드"라는 글자를 기준으로 가져오는 것은 아니므로 A3셀부터 D3셀의 영역은 범위로 지정하지 않는 것입니다. 하지만 범위를 A4:D10으로 하지 않고 A3:D10로 지정하더라도 원하는 결과는 얻을 수 있습니다. 그 이유는 F4셀부터 F8셀의 내용에는 100~700 의 제품코드 중 일부를 입력할 뿐 "제품코드"라고 입력하지는 않기 때문입니다.

 

 

3번째 인수인 Col_index_num은 2번째 인수에서 지정한 범위 중 1번째 인수가 일치할 경우 추출할 열 번호를 입력하므로 열 번호 2를 입력해 줍니다.

 

G4 셀의 경우 추출하고자 하는 제품명은 F4셀에 입력된 제품코드 100을 기준으로 A4:D10셀에서 일치하는 데이터를 찾은 경우 2번째 열의 데이터를 가져오라는 뜻입니다.

왼쪽의 데이터 중 제품명이 2번째에 있으므로 열 번호는 2가 되는 것이며, 단위를 찾을 경우는 3, 단가를 찾을 때는 4가 되는 것입니다.

 

 

4번째 인수인 Range_lookup은 값을 찾을 때 정확하게 일치하는 것을 찾을 것인지, 아니면 비슷하게 일치하는 것을 찾을 것인지를 선택하는 것으로 정확하게 일치하는 것을 찾을 때는 0 이나 FALSE를 입력하고, 비슷하게 일치하는 것을 찾을 때는 TRUE를 입력하거나 생략해도 되며 0 이 아닌 값을 넣어도 TRUE의 의미를 가지므로 통상 1을 많이 입력합니다.

 

4번째 인수인 Range_lookup은 값을 찾을 때 정확하게 일치하는 것을 찾을 것인지, 아니면 비슷하게 일치하는 것을 찾을 것인지를 선택하는 것입니다.

 

설명이 조금은 난해하다고 할 수 있는데요.

제품코드를 기준으로 제품명을 추출할 때 제품코드가 정확하게 일치하는 값을 찾을 경우 4번째 인수에 0 또는 FALSE를 입력하라는 뜻입니다.

지금 설명드리는 예제는 정확하게 일치하는 값만 가져올 것이기 때문에 4번째 인수에 0 또는 FALSE를 입력해 줍니다.

 

비슷하게 일치하는 것을 찾는다는 의미는 수우미양가의 경우처럼 특정 범위에 해당하는 값을 찾을 때 사용하는 방법입니다.

예를 들어 90점 이상은 "수", 80점 이상은 "우", 70점 이상은 "미", 60점 이상은 "양", 그 외는 "가"일 경우, 85점을 찾을 때 비슷한 값으로는 80을 찾게 되어 "우"라는 값을 가져오게 됩니다. 엑셀의 함수마법사 설명에는 비슷한 값이라고 설명되어 있지만, 우리의 방식대로 얘기하자면 해당 값보다 낮은 값 중 가장 높은 값을 찾는 방식이므로 85점보다 낮은 값 중 가장 높은 값은 80이 되어 "우"를 가져오는 방식입니다.

 

 

함수마법사의 4개 인수를 모두 입력하고 나면 위의 그림처럼 제품명으로 컴퓨터라는 값을 정확히 추출하는 것을 알 수 있습니다.

G4셀의 우측 하단에 마우스를 위치시킨 후 더블클릭하여 채우기핸들 기능으로 아래쪽 셀들도 채워줍니다.

 

 

위의 그림은 채우기 핸들을 이용해서 제품명을 모두 가져온 상태입니다.

 

 

H4 셀의 단위를 가져올 때는 앞에서 사용한 수식의 일부만 변경하면 가능합니다.

우선 조금전에 작업한 G4셀을 클릭 후 수식입력줄에서 =VLOOKUP(F4,$A$4:$D$10,2,0) 을 복사합니다. 또는 F2키를 눌러서 셀 편집 상태로 변경한 후 =VLOOKUP(F4,$A$4:$D$10,2,0) 을 복사해도 됩니다.

그런 다음 H4셀을 선택 후 수식입력줄에 붙여넣기 하거나, F2키를 눌러서 셀 편집 상태로 변경 후 붙여넣기 해 줍니다.

그런 다음 3번째 인수의 값을 2에서 3으로 변경해 줍니다.

단위 항목이 3번째 열에 있기 때문에 3으로 변경하는 것입니다.

 

 

채우기 핸들을 이용해서 나머지 셀들도 채워줍니다.

 

 

수량을 위의 그림처럼 입력해 줍니다.

 

 

J4셀의 단가도 H4셀의 단위를 구할 때와 동일한 방식으로 입력해 준 후 3번째 인수의 값을 4로 변경해 줍니다.

위의 그림은 값을 입력 후 함수마법사를 실행할 상태의 화면입니다.

 

 

채우기핸들 기능을 이용해서 J4셀부터 J8셀까지 채워줍니다.

 

 

금액은 수량*단가의 수식인 =I4*J4로 계산식을 넣어줍니다.

 

 

채우기핸들 기능을 이용해서 나머지 셀들도 채워줍니다.

 

 

이렇게 작성된 상황에서 왼쪽의 데이터가 변경된 경우 어떻게 변하는지를 확인하기 위해 스피커의 단가 30,000원을 25,000원으로 변경하면 우측의 J7셀의 값도 실시간으로 25,000원이 되는 것을 알 수 있습니다.

 

 

이상 VLOOKUP 함수 사용 방법에 대해 살펴봤습니다.

 

다음 글은 4번째 인수에 TRUE를 넣는 경우의 VLOOKUP 함수 예제이니 VLOOKUP 함수 의 또다른 사용방법을 찾는 분은 읽어보시기 바랍니다.

 

엑셀 VLOOKUP 함수 응용 예제도 활용해 보시기 바랍니다.