VLOOKUP 함수는 Excel에서 가장 많이 사용되는 함수 중 하나입니다. 이 자습서에서는 수십 개의 기본 및 고급 예제를 단계별로 사용하여 Excel에서 VLOOKUP 기능을 사용하는 방법을 소개합니다.

VLOOKUP 함수 소개 – 구문 및 인수

Excel에서 VLOOKUP 기능은 대부분의 Excel 사용자에게 강력한 기능으로 데이터 범위의 가장 왼쪽에서 값을 찾고 다음 스크린샷과 같이 지정한 열에서 동일한 행에 일치하는 값을 반환할 수 있습니다. .

VLOOKUP 함수의 구문 :

인수 :

기능 참고 :

기본 VLOOKUP 예제

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

2.1 정확한 일치 및 대략적인 일치 VLOOKUP

 2.1.1 정확한 일치 VLOOKUP 수행

일반적으로 VLOOKUP 함수와 정확히 일치하는 항목을 찾으려면 FALSE를 마지막 인수로 사용하기만 하면 됩니다.

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

아래 수식을 복사하여 빈 셀에 붙여넣고(여기서는 G2를 선택) 다음을 누릅니다. 엔터 버튼 결과를 얻는 열쇠 :

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

VLOOKUP 공식은 어떻게 작동합니까?

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

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

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

 2.1.2 대략적인 일치 VLOOKUP 수행

대략적인 일치는 데이터 범위 사이의 값을 검색하는 데 유용합니다. 정확히 일치하는 항목이 없으면 대략적인 VLOOKUP은 조회 값보다 작은 가장 큰 값을 반환합니다.

예를 들어, 다음 범위의 데이터가 있고 지정된 주문이 Orders 열에 없는 경우 B열에서 가장 가까운 할인을 얻는 방법은 무엇입니까?

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

결과를 입력할 셀에 다음 수식을 복사하여 붙여넣은 다음 채우기 핸들을 아래로 드래그하여 이 수식을 다른 셀에 적용합니다.

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

결과 :

이제 주어진 값을 기반으로 대략적인 일치 항목을 얻을 수 있습니다. 스크린샷을 참조하세요.

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

기본적으로 VLOOKUP 함수는 대소문자를 구분하지 않는 조회를 수행합니다. 즉, 소문자와 대문자를 동일하게 취급합니다. 경우에 따라 Excel에서 대/소문자 구분 조회를 수행해야 할 수도 있습니다. 일반 VLOOKUP 기능으로는 해결되지 않을 수 있습니다. 이 경우 EXACT 함수 또는 LOOKUP 및 EXACT 함수와 함께 INDEX 및 MATCH와 같은 대체 함수를 사용할 수 있습니다.

예를 들어, ID 열에 대문자 또는 소문자가있는 텍스트 문자열이 포함 된 다음 데이터 범위가 있습니다. 이제 주어진 ID 번호의 해당 수학 점수를 반환하려고합니다.

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

결과를 얻으려는 빈 셀에 아래 수식 중 하나를 복사하여 붙여넣으십시오. 그런 다음 수식 셀을 선택하고 이 수식을 채우려는 셀까지 채우기 핸들을 아래로 끕니다.

수식 1 : 수식을 붙여넣은 후 Ctrl + Shift + Enter 열쇠.

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

수식 2 : 수식을 붙여넣은 후 엔터 버튼 키.

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

결과 :

그러면 필요한 올바른 결과를 얻을 수 있습니다. 스크린샷 보기:

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

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

이 작업에 대한 세부 정보를 단계별로 알아 보려면 클릭하십시오…

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

일반적으로 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하고 마지막 일치 값을 반환하려면이 VLOOKUP 및 마지막 일치 값 반환 튜토리얼은 마지막으로 일치하는 값을 세부적으로 얻는 데 도움이 될 수 있습니다.

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

위 공식을 기억하고 이해하기 어렵다면 여기에서 쉬운 도구를 소개하겠습니다. Excel 용 Kutools그와 두 값 사이의 조회 기능을 사용하면 두 값 또는 날짜 사이의 특정 값 또는 날짜를 기준으로 해당 항목을 쉽게 반환할 수 있습니다.

1. 쿠툴 > 슈퍼 조회 > 두 값 사이의 조회 이 기능을 사용하려면
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.9 0 또는 #N/A 오류 대신 공백 또는 특정 텍스트 반환

일반적으로 VLOOKUP 함수를 사용하여 해당 값을 반환할 때 일치하는 셀이 비어 있으면 0을 반환하고 일치하는 값을 찾지 못하면 위의 그림과 같이 #N/A 오류 값을 받게 됩니다. 아래 스크린샷. 0 또는 #N/A 대신 빈 셀이나 특정 값을 표시하려면 이 0 또는 N/A 대신 공백 또는 특정 값을 반환하는 VLOOKUP 튜토리얼이 도움이 될 수 있습니다.

고급 VLOOKUP 예제

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

경우에 따라 행과 열에서 동시에 값을 검색하는 2차원 조회를 수행해야 할 수도 있습니다. 예를 들어 다음과 같은 데이터 범위가 있고 지정된 분기의 특정 제품에 대한 값을 가져와야 할 수 있습니다. 이 섹션에서는 Excel에서 이 작업을 처리하는 공식을 소개합니다.

Excel에서는 VLOOKUP 및 MATCH 함수의 조합을 사용하여 양방향 조회를 수행할 수 있습니다.

다음 수식을 빈 셀에 적용한 다음 엔터 버튼 결과를 얻으려면 키.

=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 : 수식을 붙여넣은 후 엔터 버튼 키.

=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 스마트 기능으로 둘 이상의 기준에 따른 VLOOKUP 매칭 값

반복적으로 적용해야 하는 위의 복잡한 공식을 기억하는 것은 어려울 수 있으며, 이로 인해 작업 효율성이 느려질 수 있습니다. 하지만, Excel 용 Kutools 를 제공합니다 다중 조건 조회 몇 번의 클릭만으로 하나 이상의 조건에 따라 해당 결과를 반환할 수 있는 기능입니다.

1. 쿠툴 > 슈퍼 조회 > 다중 조건 조회 이 기능을 사용하려면
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단계: 다음 수식을 적용하고 입력합니다.

결과를 출력할 빈 셀에 아래 수식을 복사하거나 입력하고 다음을 누릅니다. 엔터 버튼 키를 눌러 첫 번째 값을 가져옵니다. 그런 다음 전체 행의 데이터가 표시될 때까지 수식 셀을 오른쪽으로 끕니다.

=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 강력한 기능으로 VLOOKUP 및 첫 번째 일치 값 또는 모든 일치 값 합계

위의 공식이 기억하기 어려울 수 있습니다. 이 경우 강력한 기능을 추천해 드리겠습니다. 조회 및 합계 of Excel 용 Kutools, 이 기능을 사용하면 가능한 한 쉽게 행 또는 열에서 일치하는 첫 번째 값 또는 모든 일치 값을 Vlookup하고 합산할 수 있습니다.

1. 쿠툴 > 슈퍼 조회 > LOOKUP 및 합계 이 기능을 사용하려면
2. 그런 다음 필요에 따라 대화 상자에서 작업을 지정합니다.

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

열과 행을 모두 일치시켜야 할 때 값을 합산하려는 경우, 예를 들어 아래 스크린 샷과 같이 XNUMX 월에 제품 스웨터의 총 가치를 구할 수 있습니다.

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

다음 수식을 셀에 적용한 다음 엔터 버튼 결과를 얻으려면 키, 스크린 샷 참조 :

=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 값 이 작업을 수행합니다.

VLOOKUP 일치 값은 셀 서식 유지

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

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

모두 알고 있듯이 일반 VLOOKUP 함수는 다른 데이터 범위에서만 일치하는 값을 검색할 수 있습니다. 그러나 채우기 색상, 글꼴 색상 및 글꼴 스타일과 같은 셀 서식과 함께 해당 값을 가져오려는 경우가 있을 수 있습니다. 이 섹션에서는 Excel에서 소스 형식을 유지하면서 일치하는 값을 검색하는 방법에 대해 설명합니다.

다음 단계를 수행하여 셀 서식과 함께 해당 값을 조회하고 반환하십시오.

1단계: 코드 1을 시트 코드 모듈에 복사

1. VLOOKUP하려는 데이터가 포함된 워크시트에서 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 코드보기 상황에 맞는 메뉴에서. 스크린 샷보기 :
   
2. 열린 응용 프로그램 용 Microsoft Visual Basic 창에서 아래 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 창을 클릭합니다 끼워 넣다 > 모듈을 클릭 한 다음 아래 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. 위의 코드를 입력한 후 다음을 클릭하십시오. 도구 > 참고자료 FBI 증오 범죄 보고서 응용 프로그램 용 Microsoft Visual Basic 창문. 그런 다음 Microsoft 스크립팅 런타임 체크 박스에 참조 – VBAProject 대화 상자. 스크린 샷보기 :
2. 그런 다음 OK 대화 상자를 닫은 다음 코드 창을 저장하고 닫습니다.

4단계: 결과를 얻기 위한 수식 입력

1. 이제 워크시트로 돌아가서 다음 수식을 적용합니다. 그런 다음 채우기 핸들을 아래로 드래그하여 서식과 함께 모든 결과를 가져옵니다. 스크린샷 보기:
   

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

   
   

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

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

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 창.
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))

결과 :

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