엑셀 VLOOKUP함수 배우기 (예제 및 응용)

VLOOKUP 함수

이 문서에서는 Microsoft Excel의 VLOOKUP 함수 (함수: 하나 이상의 값을 사용하여 작업을 수행하고 하나 이상의 값을 반환하도록 미리 작성된 수식입니다. 함수를 사용하면 워크시트의 수식, 특히 길거나 복잡한 계산을 수행하는 수식을 간단하고 짧게 만들 수 있습니다.)에 사용되는 수식 구문과 이 함수를 사용하는 방법을 설명합니다.

설명

VLOOKUP 함수를 사용하면 셀 범위 (범위: 시트의 둘 이상의 셀입니다. 범위 내의 셀은 인접될 수도 있고 인접되지 않을 수도 있습니다.)의 첫 번째 열을 검색한 다음 해당 범위의 같은 행에 있는 셀 값을 반환할 수 있습니다. 예를 들어 A2:C10 범위에 포함된 사원 목록이 있고, 다음 그림과 같이 해당 범위의 첫 번째 열에 사원의 ID 번호가 저장되어 있다고 가정해 봅니다.

 

사원의 ID 번호를 알고 있는 경우 VLOOKUP 함수를 사용하여 해당 사원의 부서나 이름을 반환할 수 있습니다. 사원 번호 38의 이름을 가져오려면 =VLOOKUP(38, A2:C10, 3, FALSE) 수식을 사용하면 됩니다. 이 수식은 A2:C10 범위의 첫 번째 열에서 값 38을 검색한 다음 해당 범위의 세 번째 열 및 조회 값과 같은 행의 값을 반환합니다("이강주").

VLOOKUP에서 V는 vertical(세로)을 의미합니다. 비교값이 찾으려는 데이터의 왼쪽 열에 있으면 HLOOKUP 대신 VLOOKUP을 사용합니다.

구문

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

VLOOKUP 함수 구문에는 다음과 같은 인수 (인수: 동작, 이벤트, 메서드, 속성, 함수 또는 프로시저에 정보를 제공하는 값입니다.)가 사용됩니다.

• lookup_value    필수 요소입니다. 표 또는 범위의 첫 번째 열에서 찾을 값입니다. lookup_value 인수는 값 또는 참조일 수 있습니다. lookup_value 인수가 table_array 인수의 첫 번째 열에 있는 최소값보다 작으면 #N/A 오류 값이 반환됩니다.
• table_array    필수 요소입니다. 데이터가 들어 있는 셀 범위입니다. 범위에 대한 참조(예: A2:D8) 또는 범위 이름을 사용할 수 있습니다. table_array의 첫 번째 열의 값은 lookup_value로 검색된 값입니다. 이러한 값은 텍스트, 숫자 또는 논리값이 될 수 있습니다. 대/소문자는 구분하지 않습니다.
• col_index_num    필수 요소입니다. 반환해야 하는 값이 있는 table_array 인수의 열 번호입니다. col_index_num 인수가 1이면 table_array의 첫 번째 열 값이 반환되고, col_index_num이 2이면 table_array의 두 번째 열 값이 반환됩니다.

col_index_num 인수의 조건에 따라 다음과 같은 결과가 나타납니다.

• 1보다 작으면 #VALUE! 오류 값이 반환됩니다.
• table_array의 열 수보다 크면 #REF! 오류 값이 반환됩니다.

• range_lookup    선택 요소입니다. 정확하게 일치하는 값을 찾을 것인지, 근사값을 찾을 것인지를 결정하는 논리값입니다.
  • range_lookup이 TRUE이거나 생략되면 정확한 값이나 근사값이 반환됩니다. 정확하게 일치하는 값이 없으면 lookup_value보다 작으면서 그 다음으로 가장 큰 값이 반환됩니다.

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

자세한 내용은 범위 또는 표의 데이터 정렬을 참고하십시오.

range_lookup이 FALSE이면 table_array의 첫 번째 열 값을 정렬할 필요가 없습니다.

• range_lookup 인수가 FALSE이면 정확하게 일치하는 값만 찾습니다. table_array의 첫 번째 열에 lookup_value와 일치하는 값이 두 개 이상 있으면 먼저 발견된 값이 사용됩니다. 정확하게 일치하는 값이 없으면 #N/A 오류 값이 반환됩니다.

주의

• table_array의 첫 번째 열에서 텍스트 값을 검색할 때는 table_array의 첫 번째 열에 있는 데이터에 앞/뒤 공백을 두거나, 곧은 따옴표(' 또는 ")와 둥근 따옴표(‘ 또는 “)를 일관성 없이 사용하거나, 인쇄할 수 없는 문자를 사용하지 않도록 주의하십시오. 그럴 경우 정확하지 않은 값이나 예기치 않은 값이 반환될 수 있습니다.

자세한 내용은 CLEAN 함수 및 TRIM 함수를 참고하십시오.

• 숫자 값이나 날짜 값을 검색할 때는 table_array의 첫 번째 열에 있는 데이터를 텍스트 값으로 저장하지 않도록 주의하십시오. 그럴 경우 정확하지 않은 값이나 예기치 않은 값이 반환될 수 있습니다.
• range_lookup이 FALSE이고 lookup_value가 텍스트이면 와일드카드 문자인  물음표(?)와 별표(*) 를 lookup_value에 사용할 수 있습니다. 물음표는 한 문자에 해당하고 별표는 개수에 상관없는 임의의 문자열에 해당합니다. 실제 물음표나 별표를 찾으려면 해당 문자 앞에 물결표(~)를 입력합니다.

예제

다음 표의 예제 데이터를 복사하여 새 Excel 워크시트의 A1 셀에 붙여 넣습니다. 수식의 결과를 표시하려면 수식을 선택하고 F2 키를 누른 다음 Enter 키를 누릅니다. 필요한 경우 열 너비를 조정하면 데이터를 모두 표시할 수 있습니다.

밀도 점도 온도

0.457	3.55	500
0.525	3.25	400
0.606	2.93	300
0.675	2.75	250
0.746	2.57	200
0.835	2.38	150
0.946	2.17	100
1.09	1.95	50
1.29	1.71	0
수식	설명	결과
=VLOOKUP(1,A2:C10,2)	근사값을 사용하여 A열에서 값 1을 검색하고, A열에서 1보다 작거나 같은 값 중 최대값(즉, 0.946)을 찾은 다음, 같은 행에 있는 B열에서 값을 구합니다.	2.17
=VLOOKUP(1,A2:C10,3,TRUE)	근사값을 사용하여 A열에서 값 1을 검색하고, A열에서 1보다 작거나 같은 값 중에서 최대값(즉, 0.946)을 찾은 다음, 같은 행에 있는 C열에서 값을 구합니다.	100
=VLOOKUP(0.7,A2:C10,3,FALSE)	정확하게 일치하는 값을 사용하여 A열에서 값 0.7을 검색합니다. A열에는 정확하게 일치하는 값이 없기 때문에 오류가 반환됩니다.	#N/A
=VLOOKUP(0.1,A2:C10,2,TRUE)	정확하게 일치하는 값을 사용하여 A열에서 값 0.1을 검색합니다. 0.1은 A열의 최소값보다 작기 때문에 오류가 반환됩니다.	#N/A
=VLOOKUP(2,A2:C10,2,TRUE)	근사값을 사용하여 A열에서 값 2를 검색하고, A열에서 2보다 작거나 같은 값 중 최대값(즉, 1.29)을 찾은 다음, 같은 행에 있는 B열에서 값을 구합니다.	1.71

예제 2

상품-ID 항목 가격 마진

ST-340	유모차	\145,000	30%
BI-567	턱받이	\3,500	40%
DI-328	기저귀	\21,400	35%
WI-989	물티슈	\5,000	40%
AS-469	흡입기	\2,500	45%
수식	설명	결과
= VLOOKUP("DI-328", A2:D6, 3, FALSE) * (1 + VLOOKUP("DI-328", A2:D6, 4, FALSE))	원가에 마진 비율을 더해서 기저귀의 소매가를 계산합니다.	\28,890
= (VLOOKUP("WI-989", A2:D6, 3, FALSE) * (1 + VLOOKUP("WI-989", A2:D6, 4, FALSE))) * (1 - 20%)	소매가에서 특별 할인가를 빼서 물티슈의 판매가를 계산합니다.	\5,600
= IF(VLOOKUP(A2, A2:D6, 3, FALSE) >= 20000, "마진 " & 100 * VLOOKUP(A2, A2:D6, 4, FALSE) &"%", "원가 20,000원 이하")	상품 원가가 20,000원보다 비싸거나 같으면 "마진 nn%"가 표시됩니다. 그렇지 않을 경우 "원가 20,000원 이하"가 표시됩니다.	마진 30%
= IF(VLOOKUP(A3, A2:D6, 3, FALSE) >= 20000, "마진 " & 100 * VLOOKUP(A3, A2:D6, 4, FALSE) &"%", "원가 " & VLOOKUP(A3, A2:D6, 3, FALSE))	상품 원가가 20,000원보다 비싸거나 같으면 "마진 nn%"가 표시됩니다. 그렇지 않을 경우 "원가 n,nnn"이 표시됩니다.	원가 3,500

예제 3

ID 성 이름 제목 생년월일

1	이	강주	영업 사원	1968-12-08
2	장	선희	영업 담당 부사장	1952-02-19
3	주	진국	영업 사원	1963-08-30
4	천	용만	영업 사원	1958-09-19
5	최	영희	영업 과장	1955-03-04
6	박	광준	영업 사원	1963-07-02
수식	설명	결과
=INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1))	2004 회계 연도를 대상으로 ID가 5인 사원의 나이를 찾습니다. YEARFRAC 함수를 사용하여 회계 연도 마지막 날짜에서 생년월일을 빼고 INT 함수를 사용하여 그 결과를 정수로 표시합니다.	49
=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "사원을 찾을 수 없습니다.", VLOOKUP(5,A2:E7,2,FALSE))	ID가 5인 사원이 있으면 사원의 성을 표시합니다. 그렇지 않을 경우 "사원을 찾을 수 없습니다"라는 메시지를 표시합니다. VLOOKUP 함수가 #N/A 오류 값을 반환하면 ISNA 함수는 TRUE 값을 반환합니다.	최
=IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "사원을 찾을 수 없습니다.", VLOOKUP(15,A3:E8,2,FALSE))	ID가 15인 사원이 있으면 사원의 성을 표시합니다. 그렇지 않을 경우 "사원을 찾을 수 없습니다"라는 메시지를 표시합니다. VLOOKUP 함수가 #N/A 오류 값을 반환하면 ISNA 함수는 TRUE 값을 반환합니다.	사원을 찾을 수 없습니다.
=VLOOKUP(4,A2:E7,2,FALSE) & "" & VLOOKUP(4,A2:E7,3,FALSE) & "님은 " & VLOOKUP(4,A2:E7,4,FALSE) & "입니다."	ID가 4인 사원을 대상으로 세 개의 셀의 값을 완전한 문장으로 연결합니다.	천용만님은 영업 사원입니다.

 

 

출처:EXCEL예제