엑셀 초보자 및 고급 사용자를 위한20+ VLOOKUP 예제

VLOOKUP 함수는 Excel에서 가장 인기 있는 함수 중 하나입니다. 이 튜토리얼에서는 기본 및 고급 예제를 통해 Excel에서 VLOOKUP 함수를 사용하는 방법을 단계별로 소개합니다.

VLOOKUP 함수 소개 – 구문 및 인수

Excel에서 VLOOKUP 함수는 대부분의 Excel 사용자에게 강력한 함수입니다. 이 함수는 데이터 범위의 가장 왼쪽 열에서 값을 찾아 지정한 열에서 같은 행의 일치하는 값을 반환할 수 있도록 합니다. 아래 스크린샷과 같이 사용할 수 있습니다.

VLOOKUP 함수의 구문:

인수:

함수 참고:

기본 VLOOKUP 예제

이 섹션에서는 자주 사용하는 몇 가지 Vlookup 수식에 대해 설명하겠습니다.

2.1 정확히 일치 및 대략적으로 일치하는 VLOOKUP

 2.1.1 정확히 일치하는 VLOOKUP 수행

일반적으로 VLOOKUP 함수에서 정확히 일치하는 값을 찾으려면 마지막 인수로 FALSE를 사용하면 됩니다.

예를 들어, 특정 ID 번호를 기준으로 해당 수학 점수를 얻으려면 다음과 같이 하십시오:

아래 수식을 빈 셀에 복사하여 붙여넣고(여기서는 G2를 선택합니다), "Enter" 키를 눌러 결과를 확인하세요:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

VLOOKUP 수식은 어떻게 작동하나요?

먼저, 테이블의 가장 왼쪽 열에서 ID - C1005를 찾습니다. 위에서 아래로 이동하여 셀 A6에서 값을 찾습니다.

값을 찾으면 즉시 오른쪽으로 세 번째 열로 이동하여 그 안의 값을 추출합니다.

따라서 아래 스크린샷과 같이 결과를 얻을 수 있습니다.

 2.1.2 대략적인 일치 VLOOKUP 수행

대략적인 일치는 데이터 범위 사이에서 값을 검색하는 데 유용합니다. 정확한 일치를 찾지 못한 경우, 대략적인 VLOOKUP은 찾을 값보다 작은 가장 큰 값을 반환합니다.

예를 들어, 아래와 같은 데이터 범위가 있고 지정된 주문이 주문 열에 없을 경우, 열 B에서 가장 가까운 할인 값을 어떻게 얻을 수 있을까요?

1단계: VLOOKUP 수식을 적용하고 다른 셀에 채우기

결과를 넣고 싶은 셀에 다음 수식을 복사하여 붙여넣고, 채우기 핸들을 아래로 드래그하여 다른 셀에 이 수식을 적용하세요.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

결과:

이제 주어진 값에 따라 대략적인 일치를 얻을 수 있습니다. 스크린샷을 참조하세요:

2.2 Excel에서 대소문자 구분 VLOOKUP 수행

기본적으로 VLOOKUP 함수는 대소문자를 구분하지 않는 조회를 수행하여 소문자와 대문자를 동일하게 처리합니다. 때로는 Excel에서 대소문자를 구분하는 조회를 수행해야 할 때가 있습니다. 일반적인 VLOOKUP 함수로는 이를 해결할 수 없습니다. 이 경우, INDEX와 MATCH 함수와 함께 EXACT 함수를 사용하거나 LOOKUP과 EXACT 함수를 사용할 수 있습니다.

예를 들어, ID 열에 모두 대문자 또는 모두 소문자로 된 문자열이 포함된 다음과 같은 데이터 범위가 있습니다. 이제 주어진 ID 번호에 해당하는 수학 점수를 반환하고 싶습니다.

단계1: 하나의 수식을 적용하고 다른 셀에 채웁니다

아래 수식 중 하나를 복사하여 결과를 얻고자 하는 빈 셀에 붙여넣으세요. 그런 다음, 수식 셀을 선택하고 채우기 핸들을 드래그하여 이 수식을 적용할 셀까지 끌어내리세요.

수식1: 수식을 붙여넣은 후 "Ctrl" + "Shift" + "Enter" 키를 눌러주세요.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

수식2: 수식을 붙여넣은 후 "Enter" 키를 눌러주세요.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

결과:

그러면 필요한 정확한 결과를 얻을 수 있습니다. 스크린샷을 참조하세요:

2.3 Excel에서 오른쪽에서 왼쪽으로 VLOOKUP 값 조회

VLOOKUP 함수는 항상 데이터 범위의 가장 왼쪽 열에서 값을 검색하고 오른쪽 열에서 해당 값을 반환합니다. 특정 값을 오른쪽 열에서 조회하고 왼쪽 열에서 해당 값을 반환하는 역방향 VLOOKUP을 수행하려면 아래 스크린샷과 같이 하십시오:

이 작업에 대한 세부 정보를 단계별로 알아보세요…

2.4 Excel에서 두 번째, n번째 또는 마지막 일치하는 값을 VLOOKUP

일반적으로 Vlookup 함수를 사용할 때 여러 개의 일치하는 값이 발견되면 첫 번째로 일치하는 기록만 반환됩니다. 이 섹션에서는 데이터 범위에서 두 번째, n번째 또는 마지막 일치 값을 얻는 방법에 대해 설명하겠습니다.

 2.4.1 VLOOKUP 및 두 번째 또는 n번째 일치하는 값 반환

열 A에 이름 목록이 있고, 열 B에는 그들이 구매한 교육 과정이 있다고 가정해 보겠습니다. 이제 주어진 고객이 구매한 두 번째 또는 n번째 교육 과정을 찾으려고 합니다. 스크린샷을 참조하세요.

여기서 VLOOKUP 함수는 이 작업을 직접 해결하지 못할 수 있습니다. 하지만, 대안으로 INDEX 함수를 사용할 수 있습니다.

1단계: 수식을 다른 셀에 적용하고 채우기

예를 들어, 주어진 기준에 따라 두 번째 일치하는 값을 얻으려면, 빈 셀에 다음 수식을 입력하고 "Ctrl" + "Shift" + "Enter" 키를 함께 눌러 첫 번째 결과를 얻으세요. 그런 다음, 수식 셀을 선택하고 채우기 핸들을 드래그하여 이 수식을 채우고자 하는 셀까지 내려주세요.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

결과:

이제 주어진 이름을 기준으로 두 번째로 일치하는 모든 값이 한 번에 표시되었습니다.

 2.4.2 VLOOKUP 및 마지막으로 일치하는 값 반환

아래 스크린샷과 같이 마지막으로 일치하는 값을 찾고 싶다면, 이 'VLOOKUP And Return The Last Matching Value' 튜토리얼이 마지막으로 일치하는 값을 자세히 얻는 데 도움이 될 수 있습니다.

2.5 두 값 또는 날짜 사이의 VLOOKUP 일치 값

때때로 두 값 또는 날짜 사이의 값을 조회하고 아래 스크린샷에 표시된 대로 해당 결과를 반환하고 싶을 수 있습니다. 이 경우, 정렬된 참조 테이블을 사용하여 VLOOKUP 함수 대신 LOOKUP 함수를 사용할 수 있습니다.

 2.5.1 두 값 또는 날짜 사이의 VLOOKUP 일치 값 수식 사용

단계1: 데이터를 정렬하고 다음 수식을 적용합니다

원본 참조 테이블은 정렬된 데이터 범위여야 합니다. 그런 다음, 빈 셀에 다음 수식을 복사하거나 입력하세요. 그런 다음, 채우기 핸들을 드래그하여 필요한 다른 셀에 이 수식을 채우세요.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

결과:

이제 주어진 값에 따라 모든 일치하는 기록을 확인할 수 있습니다. 스크린샷을 참조하세요:

 2.5.2 두 값 또는 날짜 사이의 값을 VLOOKUP으로 찾기 위한 편리한 기능

위의 수식을 기억하고 이해하기 어렵다면, 여기서 간단한 도구를 소개하겠습니다 – "Kutools for Excel"의 "두 값 사이의 데이터 찾기" 기능을 사용하면, 두 값 또는 날짜 사이의 특정 값이나 날짜에 따라 해당 항목을 쉽게 반환할 수 있습니다.

1. "Kutools" > "슈퍼 LOOKUP" > "두 값 사이의 데이터 찾기"를 클릭하여 이 기능을 활성화하세요.
2. 그런 다음 데이터에 따라 대화 상자에서 계산을 지정합니다.

2.6 VLOOKUP 함수에서 부분 일치를 위한 와일드카드 사용

Excel에서 와일드카드는 VLOOKUP 함수 내에서 사용할 수 있으며, 이를 통해 조회 값의 일부와 부분적으로 일치하는 값을 찾을 수 있습니다. 예를 들어, VLOOKUP을 사용하여 조회 값의 일부를 기반으로 테이블에서 일치하는 값을 반환할 수 있습니다.

아래 스크린샷과 같이 데이터 범위가 있다고 가정할 때, 이제 이름(전체 이름이 아닌)을 기준으로 점수를 추출하고 싶습니다. Excel에서 이 작업을 어떻게 해결할 수 있을까요?

단계1: 수식을 적용하고 다른 셀에 채우기

다음 수식을 빈 셀에 복사하거나 입력한 후, 채우기 핸들을 드래그하여 필요한 다른 셀에 이 수식을 채우세요:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

결과:

그리고 모든 일치하는 점수가 아래 스크린샷과 같이 반환되었습니다:

2.7 다른 워크시트에서 VLOOKUP 값 찾기

일반적으로 하나 이상의 워크시트에서 작업해야 할 때가 있습니다. VLOOKUP 함수는 한 워크시트에서와 마찬가지로 다른 시트에서 데이터를 조회하는 데 사용할 수 있습니다.

예를 들어, 아래 스크린샷과 같이 두 개의 워크시트가 있을 때, 지정한 워크시트에서 해당 데이터를 조회하고 반환하려면 다음 단계를 수행하십시오:

단계1: 수식을 적용하고 다른 셀에 채우기

아래 수식을 원하는 항목이 일치하는 빈 셀에 입력하거나 복사하세요. 그런 다음, 이 수식을 적용할 셀까지 채우기 핸들을 아래로 드래그하세요.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

결과:

필요한 대로 해당 결과를 얻을 수 있습니다. 스크린샷을 참조하세요:

2.8 다른 통합 문서에서 VLOOKUP 값 조회

이 섹션에서는 VLOOKUP 함수를 사용하여 다른 통합 문서에서 조회하고 일치하는 값을 반환하는 방법에 대해 설명합니다.

예를 들어, 두 개의 워크북이 있다고 가정해 봅시다. 첫 번째 워크북에는 제품 목록과 해당 비용이 포함되어 있습니다. 두 번째 워크북에서는 아래 스크린샷에 표시된 대로 각 제품 항목에 대한 해당 비용을 추출하고자 합니다.

단계1: 수식을 적용합니다

사용하려는 두 통합 문서를 열고, 두 번째 통합 문서에서 결과를 넣고자 하는 셀에 다음 수식을 적용하세요. 그런 다음, 이 수식을 필요한 다른 셀로 드래그하여 복사하세요.

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

결과:

2.90 또는 #N/A 오류 대신 빈칸이나 특정 텍스트 반환

일반적으로 VLOOKUP 함수를 사용하여 해당 값을 반환할 때, 일치하는 셀이 비어 있으면0을 반환합니다. 그리고 일치하는 값을 찾을 수 없으면 아래 스크린샷에 표시된 것처럼 #N/A 오류 값을 받게 됩니다.0 또는 #N/A 대신 빈 셀이나 특정 값을 표시하고 싶다면, 이 'VLOOKUP To Return Blank Or Specific Value Instead Of0 Or N/A' 튜토리얼이 도움이 될 수 있습니다.

고급 VLOOKUP 예제

3.1 양방향 조회 (행과 열에서 VLOOKUP)

때때로, 행과 열 모두에서 값을 검색해야 하는2차원 조회를 수행해야 할 때가 있습니다. 예를 들어, 다음 데이터 범위가 있을 때 특정 분기에 특정 제품의 값을 얻어야 할 수 있습니다. 이 섹션에서는 Excel에서 이 작업을 처리하기 위한 수식을 소개합니다.

Excel에서 VLOOKUP 함수와 MATCH 함수를 조합하여 양방향 조회를 수행할 수 있습니다.

빈 셀에 다음 수식을 입력한 후 "Enter" 키를 눌러 결과를 확인하세요.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 두 개 이상의 기준에 따라 VLOOKUP 값 일치

하나의 기준에 따라 일치하는 값을 찾는 것은 쉽지만, 두 개 이상의 기준이 있을 경우 어떻게 해야 할까요?

 3.2.1 두 개 이상의 기준을 기반으로 수식을 사용하여 VLOOKUP 일치 값

이 경우, Excel의 LOOKUP 또는 MATCH 및 INDEX 함수는 이 작업을 빠르고 쉽게 해결하는 데 도움이 됩니다.

예를 들어, 아래 데이터 테이블이 있습니다. 특정 제품과 크기에 따라 일치하는 가격을 반환하려면 다음 수식이 도움이 될 수 있습니다.

단계1: 아래 수식 중 하나를 적용합니다

수식1: 다음 수식을 입력하고 "Enter" 키를 누르세요.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

수식2: 다음 수식을 입력하고 "Ctrl" + "Shift" + "Enter"를 누르세요.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

결과:

 3.2.2 Kutools for Excel을 사용하여 두 개 이상의 기준에 따라 VLOOKUP 값 일치

위의 복잡한 수식을 반복적으로 기억하고 적용하는 것은 어려울 수 있으며, 이는 작업 효율성을 저하시킬 수 있습니다. 그러나 "Kutools for Excel"은 "다중 조건 조회" 기능을 제공하여 몇 번의 클릭만으로 하나 이상의 조건에 따라 해당 결과를 반환할 수 있습니다.

1. "Kutools" > "슈퍼 LOOKUP" > "다중 조건 조회"를 클릭하여 이 기능을 활성화하세요.
2. 그런 다음 데이터에 따라 대화 상자에서 계산을 지정합니다.

3.3 VLOOKUP을 사용하여 하나 이상의 조건으로 여러 값 반환

Excel에서 VLOOKUP 함수는 값을 검색하고 여러 개의 일치하는 값이 있을 경우 첫 번째 값만 반환합니다. 때로는 행, 열 또는 단일 셀에 모든 일치하는 값을 반환하고 싶을 때가 있습니다. 이 섹션에서는 통합 문서에서 하나 이상의 조건으로 여러 일치하는 값을 반환하는 방법에 대해 설명합니다.

 3.3.1 하나 이상의 조건에 따라 가로로 모든 일치하는 값을 VLOOKUP

A1:C14 범위에 국가, 도시 및 이름이 포함된 데이터 테이블이 있다고 가정하고, 이제 아래 스크린샷과 같이 "US"에서 온 모든 이름을 가로로 반환하려고 합니다. 이 작업을 해결하려면 단계별 결과를 얻으려면 여기를 클릭하세요.

 3.3.2 하나 이상의 조건에 따라 세로로 모든 일치하는 값을 VLOOKUP

특정 기준에 따라 아래 스크린샷과 같이 Vlookup을 사용하여 모든 일치하는 값을 세로로 반환해야 하는 경우, 자세한 솔루션을 얻으려면 여기를 클릭하세요.

 3.3.3 하나 이상의 조건에 따라 모든 일치하는 값을 단일 셀에 VLOOKUP

지정된 구분 기호로 하나의 셀에 여러 일치하는 값을 Vlookup하고 반환하려면, TEXTJOIN의 새로운 기능이 이 작업을 빠르고 쉽게 해결하는 데 도움이 될 수 있습니다.

3.4 VLOOKUP을 사용하여 일치하는 셀의 전체 행 반환

이 섹션에서는 VLOOKUP 함수를 사용하여 일치하는 값의 전체 행을 검색하는 방법에 대해 설명하겠습니다.

단계1: 다음 수식을 적용합니다

결과를 출력할 빈 셀에 아래 수식을 복사하거나 입력한 후, "Enter" 키를 눌러 첫 번째 값을 얻으세요. 그런 다음, 수식 셀을 오른쪽으로 드래그하여 전체 행의 데이터가 표시될 때까지 이동하세요.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

결과:

이제 전체 행 데이터가 반환된 것을 볼 수 있습니다. 스크린샷을 참조하세요:

3.5 Excel에서 중첩된 VLOOKUP

때때로 여러 테이블에 걸쳐 상호 연결된 값을 조회해야 할 때가 있습니다. 이 경우, 여러 VLOOKUP 함수를 함께 중첩하여 최종 값을 얻을 수 있습니다.

예를 들어, 두 개의 별도 참조 테이블을 포함한 워크시트가 있습니다. 첫 번째 참조 테이블에는 모든 제품 이름과 해당 영업 사원이 나열되어 있습니다. 두 번째 참조 테이블에는 각 영업 사원의 총 판매량이 나열되어 있습니다. 이제 각 제품의 판매량을 찾고 싶다면, 다음 스크린샷과 같이 VLOOKUP 함수를 중첩하여 이 작업을 수행할 수 있습니다.

중첩된 VLOOKUP 함수의 일반 수식은 다음과 같습니다:

단계1: 다음 수식을 적용하고 채우세요

빈 셀에 다음 수식을 입력한 후, 채우기 핸들을 아래로 드래그하여 이 수식을 적용할 셀에 적용하세요.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

결과:

이제 다음 스크린샷과 같이 결과를 얻을 수 있습니다.

3.6 다른 열의 목록 데이터를 기준으로 값이 존재하는지 확인

VLOOKUP 함수는 다른 열의 데이터 목록을 기반으로 값이 존재하는지 확인하는 데 도움을 줄 수 있습니다. 예를 들어, 열 C에서 이름을 찾고 열 A에 이름이 있는지 여부에 따라 예 또는 아니오를 반환하려면 아래 스크린샷과 같이 설정할 수 있습니다.

단계1: 다음 수식을 적용합니다

빈 셀에 다음 수식을 입력한 후, 채우기 핸들을 아래로 드래그하여 이 수식을 적용할 셀에 채워주세요.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

결과:

필요한 결과를 얻을 수 있습니다. 스크린샷을 참조하세요:

3.7 VLOOKUP 및 행 또는 열에서 모든 일치하는 값 합계

숫자 데이터를 다룰 때, 테이블에서 일치하는 값을 추출하고 여러 열이나 행의 숫자를 합산해야 할 수 있습니다. 이 섹션에서는 이 작업을 수행하는 데 도움이 되는 몇 가지 수식을 소개합니다.

 3.7.1 VLOOKUP 및 일치하는 모든 값을 행 또는 여러 행에서 합계

다음 스크린샷과 같이 여러 월 동안의 판매가 포함된 제품 목록이 있다고 가정해 보겠습니다. 이제 주어진 제품을 기준으로 모든 월의 모든 주문을 합산해야 합니다.

단계1: 다음 수식을 적용합니다

다음 수식을 빈 셀에 복사하거나 입력한 후, "Ctrl" + "Shift" + "Enter" 키를 함께 눌러 첫 번째 결과를 얻으세요. 그런 다음, 채우기 핸들을 아래로 드래그하여 이 수식을 필요한 다른 셀에 복사하세요.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

결과:

첫 번째 일치하는 값의 행에 있는 모든 값이 합산되었습니다. 스크린샷을 참조하세요:

 3.7.2 VLOOKUP 및 열 또는 여러 열에서 모든 일치하는 값 합계

아래 스크린샷과 같이 특정 월의 총 값을 합산하려면, 일반적인 VLOOKUP 함수로는 해결할 수 없습니다. 이 경우, SUM, INDEX 및 MATCH 함수를 함께 사용하여 수식을 생성해야 합니다.

단계1: 다음 수식을 적용합니다

빈 셀에 아래 수식을 입력한 후, 채우기 핸들을 아래로 드래그하여 이 수식을 다른 셀에 복사합니다.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

결과:

이제 열에서 특정 월을 기준으로 첫 번째로 일치하는 값들이 합산되었습니다. 스크린샷을 참조하세요.

 3.7.3 Kutools for Excel을 사용하여 첫 번째 일치 값 또는 모든 일치 값을 VLOOKUP 및 합계

위의 수식이 기억하기 어려울 수 있습니다. 이 경우, 강력한 기능인 "Kutools for Excel"의 "룩업 및 합계"를 추천합니다. 이 기능을 사용하면 행이나 열에서 첫 번째 일치 값 또는 모든 일치 값을 Vlookup하고 합계를 쉽게 구할 수 있습니다.

1. "Kutools" > "슈퍼 LOOKUP" > "룩업 및 합계"를 클릭하여 이 기능을 활성화하세요.
2. 그런 다음 필요에 따라 대화 상자에서 계산을 지정합니다.

 3.7.4 VLOOKUP 및 모든 일치하는 값을 행과 열에서 합계

열과 행 모두 일치해야 할 때 값을 합산하려면, 예를 들어 아래 스크린샷에 표시된 것처럼3월의 제품 Sweater의 총 값을 얻고자 할 때 사용합니다.

여기에서는 SUMPRODCT 함수를 사용하여 이 작업을 수행할 수 있습니다.

다음 수식을 셀에 적용한 후 "Enter" 키를 눌러 결과를 확인하세요. 스크린샷을 참조하세요.

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 키 열을 기준으로 두 테이블을 병합하기 위한 VLOOKUP

일상 업무에서 데이터를 분석할 때, 하나 이상의 기본 키 열을 기준으로 모든 필요한 정보를 단일 테이블에 모아야 할 수 있습니다. 이 작업을 수행하기 위해 VLOOKUP 함수 대신 INDEX 및 MATCH 함수를 사용할 수 있습니다.

 3.8.1 VLOOKUP을 사용하여 하나의 키 열을 기준으로 두 테이블 병합

예를 들어, 첫 번째 테이블에는 제품과 이름 데이터가 있고, 두 번째 테이블에는 제품과 주문 데이터가 있습니다. 이제 공통 제품 열을 일치시켜 이 두 테이블을 하나의 테이블로 병합하려고 합니다.

단계1: 다음 수식을 적용합니다

다음 수식을 빈 셀에 적용하십시오. 그런 다음, 이 수식을 적용할 셀까지 채우기 핸들을 아래로 드래그하십시오.

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

결과:

이제 기본 키 열 데이터를 기준으로 첫 번째 테이블에 주문 열이 결합된 병합된 테이블을 얻게 됩니다.

 3.8.2 여러 키 열을 기준으로 두 테이블을 병합하기 위한 VLOOKUP

결합하려는 두 테이블에 여러 키 열이 있는 경우, 이러한 공통 열을 기준으로 테이블을 병합하려면 아래 단계를 따르세요.

일반 수식은 다음과 같습니다:

1단계: 다음 수식을 적용하세요

결과를 넣고 싶은 빈 셀에 아래 수식을 적용한 후, "Ctrl" + "Shift" + "Enter" 키를 함께 눌러 첫 번째 일치하는 값을 얻으세요. 스크린샷을 참조하세요.

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

단계2: 수식을 다른 셀에 채우기

그런 다음 첫 번째 수식 셀을 선택하고 채우기 핸들을 드래그하여 필요한 다른 셀에 이 수식을 복사합니다:

3.9 여러 워크시트에서 VLOOKUP으로 값 일치시키기

Excel에서 여러 워크시트에 걸쳐 VLOOKUP을 수행해야 했던 적이 있나요? 예를 들어, 세 개의 워크시트에 데이터 범위가 있고, 이러한 시트에서 기준에 따라 특정 값을 검색하려는 경우, 단계별 튜토리얼인 VLOOKUP Values Across Multiple Worksheets를 따라 이 작업을 수행할 수 있습니다.

VLOOKUP 일치하는 값 셀 형식 유지

일치하는 값을 조회할 때, 글꼴 색상, 배경색, 데이터 서식 등 원래의 셀 서식은 유지되지 않습니다. 셀이나 데이터 서식을 유지하려면, 이 섹션에서는 작업을 해결하기 위한 몇 가지 요령을 소개합니다.

4.1 VLOOKUP 일치 값 및 셀 색상, 글꼴 서식 유지

일반적으로 알려진 바와 같이, 일반 VLOOKUP 함수는 다른 데이터 범위에서 일치하는 값만 검색할 수 있습니다. 그러나 셀 서식, 예를 들어 채울 색상, 글꼴 색상, 글꼴 스타일과 함께 해당 값을 얻고 싶은 경우가 있을 수 있습니다. 이 섹션에서는 Excel에서 원본 서식을 유지하면서 일치하는 값을 검색하는 방법에 대해 논의하겠습니다.

다음 단계에 따라 조회하고 셀 서식과 함께 해당 값을 반환하세요:

단계1: 코드1을 워크시트 코드 모듈에 복사합니다

1. VLOOKUP을 수행할 데이터를 포함하는 워크시트에서 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 "코드 보기"를 선택합니다. 스크린샷을 참조하세요.
   
2. 열린 "Microsoft Visual Basic for Applications" 창에서 아래 VBA 코드를 코드 창에 복사해 주세요.
3. VBA 코드1: 찾을 값과 함께 셀 형식을 가져오는 VLOOKUP

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

단계2: 코드2를 모듈 창에 복사합니다

1. 여전히 "Microsoft Visual Basic for Applications" 창에서 "삽입" > "모듈"을 클릭한 다음, 아래의 VBA 코드2를 "모듈" 창에 복사합니다.
2. VBA 코드2: 찾을 값과 함께 셀 형식을 가져오는 VLOOKUP

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

단계3: VBAproject에 대한 옵션을 선택합니다

1. 위의 코드를 삽입한 후, "Microsoft Visual Basic for Applications" 창에서 "도구" > "참조"를 클릭합니다. 그런 다음 "참조 – VBAProject" 대화 상자에서 "Microsoft Scripting Runtime" 체크박스를 선택합니다. 스크린샷을 참조하세요.
2. 그런 다음 "확인"을 클릭하여 대화 상자를 닫고 코드 창을 저장하고 닫습니다.

단계4: 결과를 얻기 위한 수식을 입력하세요

1. 이제 워크시트로 돌아가서 다음 수식을 적용하세요. 그런 다음, 채우기 핸들을 아래로 드래그하여 모든 결과와 그 서식을 가져옵니다. 스크린샷을 참조하세요.
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 VLOOKUP 반환 값에서 날짜 형식을 유지하기

VLOOKUP 함수를 사용하여 날짜 형식의 값을 조회하고 반환할 때, 반환된 결과가 숫자로 표시될 수 있습니다. 반환된 결과에서 날짜 형식을 유지하려면, VLOOKUP 함수를 TEXT 함수로 감싸야 합니다.

단계1: 다음 수식을 적용합니다

아래 수식을 빈 셀에 적용하세요. 그런 다음, 채우기 핸들을 드래그하여 이 수식을 다른 셀에 복사하세요.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

결과:

모든 일치하는 날짜가 아래 스크린샷과 같이 반환되었습니다.

4.3 VLOOKUP에서 셀 의견 반환

Excel에서 VLOOKUP을 사용하여 일치하는 셀 데이터와 관련된 의견을 모두 검색해야 했던 적이 있습니까? 아래 스크린샷과 같이 말입니다. 그렇다면, 아래 제공된 사용자 정의 함수를 사용하여 이 작업을 수행할 수 있습니다.

단계1: 코드를 모듈에 복사합니다

1. "ALT" + "F11" 키를 눌러 "Microsoft Visual Basic for Applications" 창을 엽니다.
2. "삽입" > "모듈"을 클릭한 다음, "모듈" 창에 다음 코드를 복사하여 붙여넣습니다.
   VBA 코드: Vlookup 및 셀 의견과 함께 일치하는 값 반환:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. 그런 다음 코드 창을 저장하고 닫습니다.

단계2: 결과를 얻기 위한 수식을 입력합니다

1. 이제 다음 수식을 입력하고 채우기 핸들을 드래그하여 이 수식을 다른 셀에 복사하세요. 그러면 일치하는 값과 의견이 동시에 반환됩니다. 스크린샷을 참조하세요:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 텍스트로 저장된 숫자 VLOOKUP

예를 들어, 원본 테이블의 ID 번호가 숫자 형식으로 되어 있고 조회 셀의 ID 번호가 텍스트로 저장된 데이터 범위가 있는 경우, 일반 VLOOKUP 함수를 사용할 때 #N/A 오류가 발생할 수 있습니다. 이 경우 올바른 정보를 검색하려면 VLOOKUP 함수 내에 TEXT 및 VALUE 함수를 포함시킬 수 있습니다. 아래는 이를 위한 수식입니다:

단계1: 다음 수식을 적용하고 채우세요

다음 수식을 빈 셀에 적용한 후, 채우기 핸들을 아래로 드래그하여 이 수식을 복사하세요.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

결과:

이제 아래 스크린샷에 표시된 대로 올바른 결과를 얻을 수 있습니다.