Note: The other languages of the website are Google-translated. Back to English
로그인  \/ 
x
or
x
Register  \/ 
x

or

Excel의 기본 및 고급 예제가 포함 된 VLOOKUP 함수

Excel에서 VLOOKUP 함수는 대부분의 Excel 사용자에게 강력한 함수로, 데이터 범위의 가장 왼쪽에있는 값을 찾고 아래 스크린 샷과 같이 지정한 열에서 동일한 행에 일치하는 값을 반환하는 데 사용됩니다. . 이 자습서에서는 Excel의 기본 및 고급 예제와 함께 VLOOKUP 함수를 사용하는 방법에 대해 설명합니다.

목차 :

1. VLOOKUP 함수 소개 – 구문 및 인수

2. 기본 VLOOKUP 예제

3. 고급 VLOOKUP 예제

4. VLOOKUP 일치 값은 셀 서식 유지

5. VLOOKUP 샘플 파일 다운로드


VLOOKUP 함수 소개 – 구문 및 인수

VLOOKUP 함수의 구문 :

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

인수 :

조회 _ 값: 검색 할 값입니다. table_array 범위의 첫 번째 열에 있어야합니다.

테이블_배열: 조회 값 열과 결과 값 열이있는 데이터 범위 또는 테이블입니다.

Col_index_num: 일치하는 값이 반환 될 열의 수입니다. 테이블 배열의 가장 왼쪽 열에서 1로 시작합니다.

Range_lookup:이 VLOOKUP 함수가 정확히 일치하는지 또는 대략적인 일치를 반환할지 여부를 결정하는 논리 값입니다.

  • 대략적인 일치 - 1 / 참: 정확히 일치하는 항목이없는 경우 수식은 가장 가까운 일치 항목 (조회 값보다 작은 가장 큰 값)을 검색합니다. 이 경우 조회 열을 오름차순으로 정렬해야합니다.
    = VLOOKUP (lookup_value, table_array, col_index, TRUE)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • 정확히 일치 - 0 / 거짓: 조회 값과 정확히 동일한 값을 검색하는 데 사용됩니다. 정확히 일치하는 항목이 없으면 오류 값 # N / A가 반환됩니다.
    = VLOOKUP (lookup_value, table_array, col_index, FALSE)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

배송 시 요청 사항:

  • 1. Vlookup 함수는 왼쪽에서 오른쪽으로 만 값을 찾습니다.
  • 2. 조회 값을 기준으로 일치하는 값이 여러 개있는 경우 Vlookup 함수를 사용하여 첫 번째 일치 값만 반환됩니다.
  • 3. 조회 값을 찾을 수없는 경우 # N / A 오류 값을 반환합니다.

기본 VLOOKUP 예제

1. 정확히 일치하는 Vlookup 및 대략적인 일치 Vlookup 수행

Excel에서 정확히 일치하는 Vlookup 수행

일반적으로 Vlookup 함수와 정확히 일치하는 항목을 찾고 있다면 마지막 인수에 FALSE를 사용해야합니다.

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

1. 결과를 얻으려는 빈 셀에 아래 수식을 적용하십시오.

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

2. 그런 다음 채우기 핸들을이 수식을 채우려는 셀로 드래그하면 필요한 결과를 얻을 수 있습니다. 스크린 샷보기 :

배송 시 요청 사항:

  • 1. 위의 공식에서 F2 일치하는 값을 반환하려는 값입니다. A2 : D7 테이블 배열, 숫자 3 일치하는 값이 반환되는 열 번호이며 그릇된 정확한 일치를 나타냅니다.
  • 2. 데이터 범위에서 기준 값을 찾을 수없는 경우 오류 값 # N / A가 표시됩니다.

Excel에서 대략적인 일치 Vlookup 수행

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

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

1. 결과를 입력 할 셀에 다음 수식을 입력합니다.

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

2. 그런 다음 채우기 핸들을 셀로 드래그하여이 수식을 적용하면 주어진 값을 기준으로 대략적인 일치 항목을 얻을 수 있습니다. 스크린 샷을 참조하십시오.

배송 시 요청 사항:

  • 1. 위의 공식에서 D2 상대 정보를 반환하려는 값입니다. A2 : B9 데이터 범위, 숫자 2 일치하는 값이 반환되는 열 번호와 TRUE 대략적인 일치를 나타냅니다.
  • 2. 대략적인 일치는 특정 조회 값보다 작은 가장 큰 값을 반환합니다.
  • 3. Vlookup 함수를 사용하여 대략적인 일치 값을 얻으려면 데이터 범위의 가장 왼쪽 열을 오름차순으로 정렬해야합니다. 그렇지 않으면 잘못된 결과가 반환됩니다.

2. Excel에서 대소 문자 구분 Vlookup 수행

기본적으로 Vlookup 함수는 대소 문자를 구분하지 않는 조회를 수행합니다. 즉, 소문자와 대문자를 동일하게 취급합니다. 경우에 따라 Excel에서 대소 문자를 구분하는 조회를 수행해야 할 수도 있습니다. 색인, 일치 및 일치 함수 또는 조회 및 일치 함수가 도움이 될 수 있습니다.

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

공식 1 : EXACT, INDEX, MATCH 함수 사용

1. 결과를 얻으려는 빈 셀에 아래 배열 수식을 입력하거나 복사하십시오.

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

2. 그런 다음, Ctrl + Shift + Enter 키를 동시에 눌러 첫 번째 결과를 얻은 다음 수식 셀을 선택하고 채우기 핸들을이 수식을 채우려는 셀로 드래그하면 필요한 올바른 결과를 얻을 수 있습니다. 스크린 샷보기 :

배송 시 요청 사항:

  • 1. 위의 공식에서 A2 : A10 조회하려는 특정 값이 포함 된 열입니다. F2 조회 값입니다. C2 : C10 결과가 반환 될 열입니다.
  • 2. 일치하는 항목이 여러 개인 경우이 수식은 항상 첫 번째 일치 항목을 반환합니다.

공식 2 : 조회 및 정확한 함수 사용

1. 결과를 얻으려는 빈 셀에 아래 수식을 적용하십시오.

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

2. 그런 다음 채우기 핸들을이 수식을 복사하려는 셀로 드래그하면 아래 스크린 샷과 같이 대소 문자를 구분하는 일치 값이 표시됩니다.

배송 시 요청 사항:

  • 1. 위의 공식에서 A2 : A10 조회하려는 특정 값이 포함 된 열입니다. F2 조회 값입니다. C2 : C10 결과가 반환 될 열입니다.
  • 2. 일치하는 항목이 여러 개있는 경우이 수식은 항상 마지막 일치 항목을 반환합니다.

3. Excel에서 오른쪽에서 왼쪽으로 Vlookup 값

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

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


4. Excel에서 두 번째, n 번째 또는 마지막 일치 값 Vlookup

일반적으로 Vlookup 함수를 사용할 때 일치하는 값이 여러 개 있으면 일치하는 첫 번째 레코드 만 반환됩니다. 이 섹션에서는 Vlookup 함수를 사용하여 두 번째, n 번째 또는 마지막 일치 값을 얻는 방법에 대해 설명합니다.

Vlookup 및 두 번째 또는 n 번째 일치 값 반환

A 열에 이름 목록이 있고 B 열에 구매 한 교육 과정이 있고 이제 주어진 고객이 구입 한 두 번째 또는 n 번째 교육 과정을 찾고 있다고 가정합니다. 스크린 샷보기 :

1. 주어진 기준에 따라 두 번째 또는 n 번째 일치 값을 얻으려면 다음 배열 수식을 빈 셀에 적용하십시오.

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

2. 그런 다음 Ctrl + Shift + Enter 키를 함께 사용하여 첫 번째 결과를 얻은 다음 수식 셀을 선택하고 채우기 핸들을이 수식을 채우려는 셀로 드래그하면 주어진 이름을 기반으로 일치하는 두 번째 값이 모두 한 번에 표시됩니다. 스크린 샷 참조 :

참고 :

  • 이 공식에서 A2 : A14 조회를위한 모든 값이 포함 된 범위입니다. B2 : B14 반환하려는 일치 값의 범위입니다. E2 조회 값이고 마지막 숫자입니다. 2 얻고 자하는 두 번째 일치 값을 나타내며, 세 번째 일치 값을 반환하려면 필요에 따라 3으로 변경하면됩니다.

Vlookup 및 마지막 일치 값 반환

아래 스크린 샷과 같이 vlookup하고 마지막 일치 값을 반환하려면이 Vlookup 및 마지막 일치 값 반환 튜토리얼은 세부적으로 마지막 일치 값을 얻는 데 도움이 될 수 있습니다.


5. 주어진 두 값 또는 날짜 사이의 Vlookup 일치 값

때로는 두 값 또는 날짜 사이의 값을 조회하고 아래 스크린 샷과 같이 해당 결과를 반환 할 수 있습니다.이 경우 LOOKUP 함수와 정렬 된 테이블을 사용할 수 있습니다.

주어진 두 값 또는 수식을 사용하는 날짜 사이의 Vlookup 일치 값

1. 첫째, 원래 테이블은 정렬 된 데이터 범위 여야합니다. 그런 다음 빈 셀에 다음 수식을 복사하거나 입력하십시오.

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

2. 그런 다음 채우기 핸들을 드래그하여이 수식을 필요한 다른 셀로 채우면 이제 주어진 값을 기반으로 일치하는 모든 레코드를 얻을 수 있습니다. 스크린 샷을 참조하십시오.

배송 시 요청 사항:

  • 1. 위의 공식에서 A2 : A6 더 작은 값의 범위이며 B2 : B6 데이터 범위에서 더 큰 숫자의 범위입니다. E2 해당 값을 얻으려는 주어진 값입니다. C2 : C6 추출하려는 열 데이터입니다.
  • 2.이 공식은 아래 스크린 샷과 같이 두 날짜 사이에 일치하는 값을 추출하는 데에도 사용할 수 있습니다.

유용한 기능을 사용하여 주어진 두 값 또는 날짜 간의 Vlookup 일치 값

위의 공식이 힘들다면 여기에 쉬운 도구를 소개하겠습니다. Excel 용 Kutools그와 두 값 사이의 조회 기능을 사용하면 수식을 기억하지 않고 두 값 또는 날짜 사이의 특정 값 또는 날짜를 기반으로 해당 항목을 반환 할 수 있습니다.   지금 Excel 용 Kutools를 다운로드하려면 클릭하십시오!


6. Vlookup 함수에서 부분 일치에 와일드 카드 사용

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

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

1. 일반 Vlookup 기능이 제대로 작동하지 않습니다. 텍스트 또는 셀 참조를 와일드 카드로 조인해야합니다. 다음 수식을 빈 셀에 복사하거나 입력하십시오.

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

2. 그런 다음 채우기 핸들을 드래그하여이 수식을 필요한 다른 셀로 채우면 아래 스크린 샷과 같이 일치하는 모든 점수가 반환됩니다.

배송 시 요청 사항:

  • 1. 위의 공식에서 E2 &”*” 조회 값입니다. E2 그리고 * 와일드 카드 ( "*"는 임의의 한 문자 또는 임의의 문자를 나타냄), A2 : C11 조회 범위, 숫자 3 반환 할 값이 포함 된 열입니다.
  • 2. Vlookup 와일드 카드 사용시 Vlookup 함수의 마지막 인수에 대해 FALSE 또는 0으로 정확히 일치 모드를 설정해야합니다.

팁 :

1. 특정 값으로 끝나는 일치하는 값을 찾아 반환하려면 다음 공식을 적용하십시오. =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. 지정된 텍스트가 텍스트 문자열의 앞, 뒤 또는 중간에 있든 관계없이 텍스트 문자열의 일부를 기반으로 일치하는 값을 조회하고 반환하려면 셀 참조 또는 텍스트 주위에 두 개의 * 문자를 결합하면됩니다. 이 공식으로 수행하십시오. =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. 다른 워크 시트의 Vlookup 값

일반적으로 둘 이상의 워크 시트로 작업해야 할 수 있습니다. Vlookup 함수를 사용하여 하나의 워크 시트에서와 동일하게 다른 시트의 데이터를 조회 할 수 있습니다.

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

1. 일치하는 항목을 가져올 빈 셀에 아래 수식을 입력하거나 복사하십시오.

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

2. 그런 다음 채우기 핸들을이 수식을 적용 할 셀로 드래그하면 필요에 따라 해당 결과를 얻을 수 있습니다 (스크린 샷 참조).

참고 : 위의 공식에서 :

  • A2 조회 값을 나타냅니다.
  • 데이터 시트 데이터를 조회 할 워크 시트의 이름입니다 (시트 이름에 공백이나 구두점 문자가 포함 된 경우 시트 이름을 작은 따옴표로 묶어야합니다. 그렇지 않으면 = VLOOKUP (A2, 데이터 시트! $ A $ 2 : $ C $ 15,3,0));
  • A2 : C15 데이터를 검색하는 데이터 시트의 데이터 범위입니다.
  • 3 반환하려는 일치 데이터가 포함 된 열 번호입니다.

8. 다른 통합 문서의 Vlookup 값

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

예를 들어 첫 번째 통합 문서에는 제품 및 비용 목록이 포함되어 있으므로 아래 스크린 샷과 같이 제품 항목을 기반으로 두 번째 통합 문서에서 해당 비용을 추출하려고합니다.

1. 다른 통합 문서에서 상대 비용을 검색하려면 먼저 사용할 통합 문서를 모두 연 다음 결과를 넣을 셀에 다음 수식을 적용합니다.

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

2. 그런 다음이 수식을 필요한 다른 셀로 끌어서 복사합니다 (스크린 샷 참조).

배송 시 요청 사항:

  • 1. 위 공식에서 :
    B2 조회 값을 나타냅니다.
    [제품 목록 .xlsx] 시트 1 데이터를 조회 할 통합 문서 및 워크 시트의 이름입니다. 통합 문서에 대한 참조는 대괄호로 묶이고 전체 통합 문서 + 시트는 작은 따옴표로 묶습니다.
    A2 : B6 데이터를 검색하는 다른 통합 문서의 워크 시트에있는 데이터 범위입니다.
    2 반환하려는 일치 데이터가 포함 된 열 번호입니다.
  • 2. 조회 통합 문서가 닫히면 다음 스크린 샷과 같이 조회 통합 문서의 전체 파일 경로가 수식에 표시됩니다.

9. Vlookup 및 0 또는 # N / A 오류 값 대신 공백 또는 특정 텍스트 반환

일반적으로 vlookup 함수를 적용하여 해당 값을 반환 할 때 일치하는 셀이 비어 있으면 0을 반환하고 일치하는 값을 찾을 수없는 경우 아래 스크린 샷과 같이 오류 # N / A 값이 표시됩니다. 0 또는 # N / A 값을 빈 셀이나 원하는 다른 값으로 표시하는 대신 0 또는 N / A 대신 공백 또는 특정 값을 반환하는 Vlookup 튜토리얼은 단계별로 호의를 베풀 수 있습니다.


고급 VLOOKUP 예제

1. Vlookup 함수를 사용한 양방향 조회 (행 및 열의 Vlookup)

때로는 2 차원 조회가 필요할 수 있습니다. 즉, 행과 열에서 동시에 Vlookup을 수행해야합니다. 다음과 같은 데이터 범위가 있고 지금은 지정된 분기에 특정 제품에 대한 값을 가져와야 할 수 있습니다. 이 섹션에서는 Excel에서이 작업을 처리하기위한 몇 가지 공식을 소개합니다.

공식 1 : VLOOKUP 및 MATCH 함수 사용

Excel에서는 VLOOKUP 및 MATCH 함수의 조합을 사용하여 양방향 조회를 수행 할 수 있습니다. 다음 수식을 빈 셀에 적용한 다음 엔터 버튼 결과를 얻으려면 키.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

참고 : 위의 공식에서 :

  • H1: 해당 값을 기반으로하는 열의 조회 값입니다.
  • A2 : E6: 행 헤더를 포함한 데이터 범위;
  • H2: 해당 값을 기반으로하려는 행의 조회 값입니다.
  • A1 : E1: 열 헤더의 셀.

공식 2 : INDEX 및 MATCH 함수 사용

다음은 2 차원 조회를 수행하는 데 도움이되는 또 다른 공식입니다. 아래 공식을 적용한 다음 엔터 버튼 필요한 결과를 얻으려면 키를 누르십시오.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

참고 : 위의 공식에서 :

  • B2 : E6: 일치 항목을 반환 할 데이터 범위입니다.
  • H1: 해당 값을 기반으로하는 열의 조회 값입니다.
  • A2 : A6: 행 머리글에는 찾고자하는 제품이 포함됩니다.
  • H2: 해당 값을 기반으로하려는 행의 조회 값입니다.
  • B1 : E1: 열 머리글에는 찾고자하는 분기가 포함됩니다.

2. 둘 이상의 기준에 따른 Vlookup 일치 값

하나의 기준에 따라 일치하는 값을 쉽게 찾을 수 있지만 두 개 이상의 기준이있는 경우 어떻게 할 수 있습니까? Excel의 LOOKUP 또는 MATCH 및 INDEX 함수를 사용하면이 작업을 빠르고 쉽게 해결할 수 있습니다.

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

공식 1 : LOOKUP 함수 사용

결과를 얻으려는 셀에 아래 수식을 적용한 다음 Enter 키를 눌러 스크린 샷을 참조하십시오.

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

배송 시 요청 사항:

  • 1. 위 공식에서 :
    A2 : A12 = G1: 범위 A1 : A2에서 G12의 기준을 검색하는 것을 의미합니다.
    B2 : B12 = G2: 범위 B2 : B2에서 G12의 기준을 검색하는 것을 의미합니다.
    D2 : D12: 해당 값을 반환 할 범위입니다.
  • 2. 기준이 세 개 이상인 경우 다음과 같은 다른 기준을 공식에 ​​결합하기 만하면됩니다. =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

공식 2 : INDEXT 및 MATCH 함수 사용

Index 및 Match 함수의 조합을 사용하여 여러 기준에 따라 일치하는 값을 반환 할 수도 있습니다. 다음 공식을 복사하거나 입력하십시오.

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

그런 다음 Ctrl + Shift + Enter 키를 함께 눌러 필요한 상대 값을 가져옵니다. 스크린 샷보기 :

배송 시 요청 사항:

  • 1. 위 공식에서 :
    A2 : A12 = G1: 범위 A1 : A2에서 G12의 기준을 검색하는 것을 의미합니다.
    B2 : B12 = G2: 범위 B2 : B2에서 G12의 기준을 검색하는 것을 의미합니다.
    D2 : D12: 해당 값을 반환 할 범위입니다.
  • 2. 기준이 세 개 이상인 경우 다음과 같이 새 기준을 공식에 ​​결합하기 만하면됩니다. =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. 하나 이상의 조건이있는 여러 일치 값을 반환하는 Vlookup

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

하나 이상의 조건을 기준으로 모든 일치 값을 가로로 Vlookup

하나의 조건을 기준으로 모든 일치 값을 수평으로 조회합니다.

Vlookup하고 하나의 특정 값을 기준으로 일치하는 모든 값을 수평으로 반환하려면 일반 수식은 다음과 같습니다.

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
주의 사항: m 반환 범위에서 1을 뺀 첫 번째 셀의 행 번호입니다.
      n 첫 번째 수식 셀에서 1을 뺀 열 번호입니다.

1. 아래 수식을 빈 셀에 적용한 다음 Ctrl + Shift + Enter 키를 함께 사용하여 첫 번째 일치 값을 얻으려면 스크린 샷을 참조하십시오.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. 그런 다음 첫 번째 수식 셀을 선택하고 빈 셀이 표시되고 모든 해당 항목이 추출 될 때까지 채우기 핸들을 오른쪽 셀로 드래그합니다. 스크린 샷을 참조하십시오.

팁 :

반환 된 목록에 중복 된 일치 값이있는 경우 중복을 무시하려면이 수식을 사용한 다음 엔터 버튼 첫 번째 결과를 얻으려면 : =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

이 공식을 계속 입력하십시오. =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") 첫 번째 결과 옆의 셀에 넣은 다음 Ctrl + Shift + Enter 키를 모아 두 번째 결과를 얻은 다음이 수식을 오른쪽 셀로 드래그하여 빈 셀이 표시 될 때까지 다른 모든 일치 값을 가져옵니다. 스크린 샷을 참조하십시오.


두 개 이상의 조건을 기준으로 모든 일치 값을 가로로 Vlookup :

Vlookup하고 더 구체적인 값을 기준으로 일치하는 모든 값을 수평으로 반환하려면 일반 수식은 다음과 같습니다.

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
주의 사항: m 반환 범위에서 1을 뺀 첫 번째 셀의 행 번호입니다.
      n 첫 번째 수식 셀에서 1을 뺀 열 번호입니다.

1. 결과를 출력 할 빈 셀에 다음 수식을 적용합니다.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. 그런 다음 수식 셀을 선택하고 빈 셀이 표시되고 특정 기준에 따라 일치하는 모든 값이 반환 될 때까지 채우기 핸들을 오른쪽 셀로 드래그합니다 (스크린 샷 참조).

주의 사항: 더 많은 기준을 보려면 다음과 같이 lookup_value 및 lookup_range를 수식에 조인하기 만하면됩니다. =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


수직으로 하나 이상의 조건을 기반으로 모든 일치 값을 Vlookup

수직으로 하나의 조건에 따라 일치하는 모든 값을 Vlookup :

Vlookup하고 하나의 특정 값을 기준으로 일치하는 모든 값을 세로로 반환하려면 일반 수식은 다음과 같습니다.

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
주의 사항: m 반환 범위에서 1을 뺀 첫 번째 셀의 행 번호입니다.
      n 첫 번째 수식 셀에서 1을 뺀 행 번호입니다.

1. 결과를 얻을 셀에 다음 수식을 복사하거나 입력 한 다음 Ctrl + Shift + Enter 키를 함께 사용하여 첫 번째 일치 값을 얻으려면 스크린 샷을 참조하십시오.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. 그런 다음 첫 번째 수식 셀을 선택하고 빈 셀이 표시되고 모든 해당 항목이 열에 나열 될 때까지 채우기 핸들을 다른 셀로 드래그합니다 (스크린 샷 참조).

팁 :

반환 된 일치 값의 중복을 무시하려면 다음 공식을 사용하십시오. =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

그런 다음 Ctrl + Shift + Enter 키를 함께 사용하여 첫 번째로 일치하는 값을 얻은 다음 빈 셀이 표시 될 때까지이 수식 셀을 다른 셀로 끌어다 놓으면 필요한 결과를 얻을 수 있습니다.


두 개 이상의 조건을 기준으로 모든 일치 값을 수직으로 Vlookup :

Vlookup하고 더 구체적인 값을 기준으로 일치하는 모든 값을 세로로 반환하려면 일반 수식은 다음과 같습니다.

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
주의 사항: m 반환 범위에서 1을 뺀 첫 번째 셀의 행 번호입니다.
      n 첫 번째 수식 셀에서 1을 뺀 행 번호입니다.

1. 아래 수식을 빈 셀에 복사 한 다음 Ctrl + Shift + Enter 키를 함께 사용하여 첫 번째 일치 항목을 얻습니다.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. 그런 다음 빈 셀이 표시 될 때까지 공식 셀을 다른 셀로 드래그합니다 (스크린 샷 참조).

주의 사항: 더 많은 기준을 보려면 다음과 같이 lookup_value 및 lookup_range를 수식에 조인하기 만하면됩니다. =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


둘 이상의 조건을 기반으로 일치하는 모든 값을 단일 셀로 Vlookup

Vlookup하고 일치하는 여러 값을 지정된 구분 기호가있는 단일 셀로 반환하려는 경우 TEXTJOIN의 새로운 기능을 사용하면이 작업을 빠르고 쉽게 해결할 수 있습니다.

하나의 조건에 따라 일치하는 모든 값을 단일 셀로 Vlookup :

아래의 간단한 수식을 빈 셀에 적용한 다음 Ctrl + Shift + Enter 결과를 얻으려면 키를 함께 사용하십시오.

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

팁 :

반환 된 일치 값의 중복을 무시하려면 다음 공식을 사용하십시오. =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


두 개 이상의 조건에 따라 일치하는 모든 값을 단일 셀로 Vlookup :

일치하는 모든 값을 단일 셀로 반환 할 때 여러 조건을 처리하려면 아래 수식을 적용한 다음 Ctrl + Shift + Enter 결과를 얻으려면 키를 함께 사용하십시오.

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

배송 시 요청 사항:

1. TEXTJOIN 기능은 Excel 2019 및 Office 365에서만 사용할 수 있습니다.

2. Excel 2016 및 이전 버전을 사용하는 경우 아래 문서의 사용자 정의 기능을 사용하십시오.


4. 일치하는 셀의 전체 또는 전체 행을 반환하는 Vlookup

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

1. 결과를 출력하려는 ​​빈 셀에 아래 수식을 복사하거나 입력하고 엔터 버튼 첫 번째 값을 얻으려면 키를 누르십시오.

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

2. 그런 다음 전체 행의 데이터가 표시 될 때까지 수식 셀을 오른쪽으로 끕니다 (스크린 샷 참조).

주의 사항: 위의 공식에서 F2 기준으로 전체 행을 반환하려는 조회 값입니다. A1 : D12 사용할 데이터 범위입니다. A1 데이터 범위 내의 첫 번째 열 번호를 나타냅니다.

팁 :

일치하는 값을 기반으로 여러 행이 발견 된 경우 해당 행을 모두 반환하려면 다음 수식을 적용하세요. =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), 다음을 누릅니다. Ctrl + Shift + Enter 키를 함께 사용하여 첫 번째 결과를 얻은 다음 채우기 핸들을 셀로 바로 드래그합니다. 스크린 샷을 참조하십시오.

그런 다음 채우기 핸들을 셀을 가로 질러 아래로 드래그하여 아래 스크린 샷과 같이 일치하는 모든 행을 가져옵니다.


5. Excel에서 여러 Vlookup 기능 (중첩 된 Vlookup) 수행

때로는 여러 테이블에서 값을 조회하고 싶을 수 있습니다. 테이블에 아래 스크린 샷과 같이 주어진 조회 값이 포함되어있는 경우,이 경우 하나 이상의 Vlookup 함수를 IFERROR 함수와 결합하여 다중 조회를 수행 할 수 있습니다.

중첩 된 Vlookup 함수의 일반 공식은 다음과 같습니다.

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

참고 :

  • lookup_value: 당신이 찾고있는 가치;
  • Table1, Table2, Table3, ... : 조회 값과 반환 값이 존재하는 테이블;
  • 대장균의 뜻: 일치하는 값을 반환 할 테이블의 열 번호입니다.
  • 0: 정확히 일치하는 데 사용됩니다.

1. 결과를 입력하려는 빈 셀에 다음 수식을 적용하십시오.

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. 그런 다음 채우기 핸들을이 수식을 적용하려는 셀로 드래그하면 아래 스크린 샷과 같이 일치하는 모든 값이 반환됩니다.

배송 시 요청 사항:

  • 1. 위의 공식에서 J3 당신이 찾고있는 가치입니다; A3 : B7, D3 : E7, G3 : H7 조회 값과 반환 값이 존재하는 테이블 범위입니다. 수 2 일치하는 값을 반환 할 범위의 열 번호입니다.
  • 2. 조회 값을 찾을 수없는 경우 오류 값이 표시됩니다. 오류를 읽을 수있는 텍스트로 바꾸려면 다음 공식을 사용하십시오. =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. 다른 열의 목록 데이터를 기반으로 값이 있는지 확인하는 Vlookup

Vlookup 함수는 또한 다른 목록을 기반으로 값이 존재하는지 확인하는 데 도움이 될 수 있습니다. 예를 들어, C 열에서 이름을 찾고 아래 스크린 샷과 같이 A 열에서 이름이 발견되거나없는 경우 Yes 또는 No를 반환하려는 경우 표시됩니다.

1. 빈 셀에 다음 수식을 적용하십시오.

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

2. 그런 다음 채우기 핸들을이 수식을 채우려는 셀로 드래그하면 필요한 결과를 얻을 수 있습니다. 스크린 샷을 참조하십시오.

주의 사항: 위의 공식에서 C2 확인하려는 조회 값입니다. A2 : A10 검색 값을 찾을 수있는 범위 목록입니다. 수 1 범위에서 값을 가져 오려는 열 번호입니다.


7. Vlookup 및 행 또는 열의 모든 일치 값 합계

숫자 데이터로 작업하는 경우 때로는 테이블에서 일치하는 값을 추출 할 때 여러 열 또는 행의 숫자를 합산해야 할 수도 있습니다. 이 섹션에서는 Excel에서이 작업을 완료하는 몇 가지 공식을 소개합니다.

Vlookup 및 한 행 또는 여러 행의 모든 ​​일치 값 합계

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

Vlookup 및 행의 첫 번째 일치 값 합계 :

1. 다음 수식을 빈 셀에 복사하거나 입력 한 다음 Ctrl + Shift + Enter 첫 번째 결과를 얻으려면 키를 함께 사용하십시오.

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

2. 그런 다음 채우기 핸들을 아래로 드래그하여이 수식을 필요한 다른 셀에 복사하면 첫 번째 일치하는 값 행의 모든 ​​값이 합산됩니다. 스크린 샷을 참조하십시오.

주의 사항: 위 공식에서 : H2 찾고있는 값을 포함하는 셀입니다. A2 : F9 조회 값과 일치 값을 포함하는 데이터 범위 (열 헤더 없음)입니다. 수 2,3,4,5,6 {{}} 범위의 합계를 계산하는 데 사용되는 열 번호입니다.


Vlookup 및 여러 행의 모든 ​​일치 값 합계 :

위의 수식은 첫 번째 일치 값에 대한 행의 값만 합산 할 수 있습니다. 여러 행에있는 모든 일치 항목을 합산하려면 다음 수식을 사용한 다음 채우기 핸들을이 수식을 적용 할 셀로 드래그하면 원하는 결과를 얻을 수 있습니다 (스크린 샷 참조).

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

주의 사항: 위 공식에서 : H2 찾고있는 조회 값입니다. A2 : A9 조회 값을 포함하는 행 헤더입니다. B2 : F9 합계 할 숫자 값의 데이터 범위입니다.


Vlookup 및 한 열 또는 여러 열의 모든 일치 값 합계

Vlookup 및 열에서 첫 번째 일치 값 합계 :

아래 스크린 샷에 표시된대로 특정 월의 총 가치를 합산하려는 경우.

아래 수식을 빈 셀에 적용한 다음 채우기 핸들을 아래로 끌어이 수식을 다른 셀에 복사합니다. 이제 열의 특정 월을 기준으로 일치하는 첫 번째 값이 합산되었습니다. 스크린 샷 참조 :

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

주의 사항: 위 공식에서 : H2 찾고있는 조회 값입니다. B1 : F1 조회 값을 포함하는 열 머리글입니다. B2 : F9 합계 할 숫자 값의 데이터 범위입니다.


Vlookup 및 여러 열의 모든 일치 값 합계 :

여러 열에서 일치하는 모든 값을 Vlookup하고 합계하려면 다음 수식을 사용해야합니다.

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

주의 사항: 위 공식에서 : H2 찾고있는 조회 값입니다. B1 : F1 조회 값을 포함하는 열 머리글입니다. B2 : F9 합계 할 숫자 값의 데이터 범위입니다.


강력한 기능으로 Vlookup 및 첫 번째 일치 값 또는 모든 일치 값 합계

위의 공식은 기억하기 어려울 수 있습니다.이 경우 편리한 기능을 추천합니다. 조회 및 합계 of Excel 용 Kutools,이 기능을 사용하면 가능한 한 쉽게 결과를 얻을 수 있습니다.    지금 Excel 용 Kutools를 다운로드하려면 클릭하십시오!


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

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

다음 수식을 셀에 적용한 다음 Enter 키를 눌러 결과를 얻으십시오. 스크린 샷을 참조하십시오.

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

주의 사항: 위 공식에서 : B2 : F9 합계하려는 숫자 값의 데이터 범위입니다. B1 : F1 is 열 머리글에는 합산 할 조회 값이 포함됩니다. I2 찾고있는 열 머리글 내의 조회 값입니다. A2 : A9 합계 할 기준이되는 조회 값이 행 머리글에 포함됩니다. H2 찾고있는 행 머리글 내의 조회 값입니다.


8. 하나 이상의 키 열을 기반으로 두 테이블을 병합하는 Vlookup

일상적인 작업에서 데이터를 분석 할 때 하나 이상의 키 열을 기반으로 필요한 모든 정보를 단일 테이블에 수집해야 할 수 있습니다. 이 작업을 해결하기 위해 Vlookup 기능도 도움이 될 수 있습니다.

하나의 키 열을 기반으로 두 테이블을 병합하는 Vlookup

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

공식 1 : VLOOKUP 함수 사용

키 열을 기준으로 두 테이블을 하나로 병합하려면 다음 수식을 결과를 얻고 자하는 빈 셀에 적용한 다음 채우기 핸들을이 수식을 적용 할 셀로 드래그하면됩니다. 키 열 데이터를 기반으로 첫 번째 테이블 데이터에 결합되는 순서 열이있는 병합 된 테이블을 가져옵니다.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

주의 사항: 위의 공식에서 A2 당신이 찾고있는 가치입니다. E2 : F8 검색 할 테이블, 숫자 2 값을 검색 할 테이블의 열 번호입니다.

공식 2 : INDEX 및 MATCH 함수 사용

오른쪽에 공통 데이터가 있고 두 번째 테이블의 왼쪽 열에 반환 된 데이터가 있으면 순서 열을 병합하기 위해 Vlookup 함수가 작업을 수행 할 수 없습니다. 오른쪽에서 왼쪽으로 조회하려면 INDEX 및 MATCH 함수를 사용하여 Vlookup 함수를 대체 할 수 있습니다.

아래 수식을 빈 셀에 복사하거나 입력 한 다음 수식을 열 아래로 복사하면 순서 열이 첫 번째 테이블에 결합되었습니다. 스크린 샷 참조 :

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

주의 사항: 위의 공식에서 A2 찾고있는 조회 값입니다. E2 : E8 반환하려는 데이터 범위입니다. F2 : F8 조회 값을 포함하는 조회 범위입니다.


여러 키 열을 기반으로 두 테이블을 병합하는 Vlookup

조인하려는 두 테이블에 여러 키 열이있는 경우 이러한 공통 열을 기반으로 테이블을 병합하려면 INDEX 및 MATCH 함수가 도움이 될 수 있습니다.

여러 키 열을 기반으로 두 테이블을 병합하는 일반 공식은 다음과 같습니다.

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. 결과를 입력 할 빈 셀에 아래 수식을 적용한 후 Ctrl + Shift + Enter 키를 함께 사용하여 첫 번째 일치 값을 얻으려면 스크린 샷을 참조하십시오.

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

주의 사항: 위 공식에서 셀 참조가 나타내는 것은 아래 스크린 샷과 같습니다.

2그런 다음 첫 번째 수식 셀을 선택하고 채우기 핸들을 끌어 필요에 따라이 수식을 다른 셀에 복사합니다.

: Excel 2016 이상 버전에서는 파워 쿼리 키 열을 기반으로 두 개 이상의 테이블을 하나로 병합하는 기능. 단계별로 세부 사항을 알고 클릭하십시오.

9. 여러 워크 시트에서 Vlookup 일치 값

여러 워크 시트에서 Vlookup 값을 시도한 적이 있습니까? 데이터 범위가 포함 된 다음 세 개의 워크 시트가 있다고 가정하고 이제이 세 가지 워크 시트의 기준에 따라 해당 값의 일부를 가져 와서 아래 스크린 샷과 같은 결과를 얻으려고합니다. 이 경우 여러 워크 시트의 Vlookup 값 튜토리얼은 단계별로 호의를 베풀 수 있습니다.


VLOOKUP 일치 값은 셀 서식 유지

1. 조회 값과 함께 셀 서식 (셀 색상, 글꼴 색상)을 가져 오는 Vlookup

우리 모두 알다시피 일반 Vlookup 함수는 다른 데이터 범위에서 일치하는 값을 반환하는 데만 도움이 될 수 있지만 때로는 채우기 색상, 글꼴 색상, 글꼴 스타일과 같은 셀 서식과 함께 해당 값을 반환 할 수 있습니다. 아래 스크린 샷과 같이. 이 섹션에서는 Excel에서 반환 된 값으로 셀 서식을 가져 오는 방법에 대해 설명합니다.

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

1. 워크 시트에 Vlookup 할 데이터가 포함되어 있습니다. 시트 탭을 마우스 오른쪽 단추로 클릭하고 코드보기 상황에 맞는 메뉴에서. 스크린 샷보기 :

2. 열린 응용 프로그램 용 Microsoft Visual Basic 창에서 VBA 코드 아래를 코드 창에 복사하십시오.

VBA 코드 1 : 조회 값과 함께 셀 서식을 가져 오는 Vlookup

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

3. 여전히 응용 프로그램 용 Microsoft Visual Basic 창을 클릭합니다 끼워 넣다 > 모듈을 클릭 한 다음 아래 VBA 코드 2를 모듈 창에 복사합니다.

VBA 코드 2 : 조회 값과 함께 셀 서식을 가져 오는 Vlookup

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. 위의 코드를 삽입 한 후 도구 > 참조 에서 응용 프로그램 용 Microsoft Visual Basic 창문. 그런 다음 Microsoft 스크립트 런타임 체크 박스에 참조 – VBAProject 대화 상자. 스크린 샷보기 :

5. 그런 다음 OK 대화 상자를 닫은 다음 코드 창을 저장하고 닫으려면 이제 워크 시트로 돌아가 다음 수식을 적용합니다. =LookupKeepFormat(E2,$A$1:$C$10,3) 결과를 출력하려는 ​​빈 셀에 넣은 다음 Enter 키를 누릅니다. 스크린 샷보기 :

주의 사항: 위의 공식에서 E2 당신이 찾을 가치입니다. A1 : C10 테이블 범위 및 숫자 3 일치 값을 반환 할 테이블의 열 번호입니다.

6. 그런 다음 첫 번째 결과 셀을 선택하고 채우기 핸들을 아래로 끌어 서식과 함께 모든 결과를 가져옵니다. 스크린 샷을 참조하십시오.


2. Vlookup 반환 값에서 날짜 형식 유지

일반적으로 Vloook 함수를 사용하여 일치하는 날짜 형식 값을 조회하고 반환 할 때 아래 스크린 샷과 같이 일부 숫자 형식이 표시됩니다. 반환 된 결과에서 날짜 형식을 유지하려면 TEXT 함수를 Vlookup 함수로 묶어야합니다.

아래 수식을 빈 셀에 적용한 다음 채우기 핸들을 끌어이 수식을 다른 셀에 복사하면 아래 스크린 샷과 같이 일치하는 모든 날짜가 반환됩니다.

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

주의 사항: 위의 공식에서 E2 룩 값입니다. A2 : C9 조회 범위, 숫자 3 값을 반환 할 열 번호입니다. mm/dd/yyy 유지하려는 날짜 형식입니다.


3. Vlookup 및 셀 주석과 일치하는 값 반환

일치하는 셀 데이터뿐만 아니라 다음 스크린 샷과 같이 Excel에서도 셀 주석을 반환하기 위해 Vlookup을 시도한 적이 있습니까? 이 작업을 해결하기 위해 아래 사용자 정의 함수가 도움이 될 수 있습니다.

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. 그런 다음 코드 창을 저장하고 닫고 다음 공식을 입력합니다. =vlookupcomment(D2,$A$2:$B$9,2,FALSE) 빈 셀에 넣어 결과를 찾은 다음 채우기 핸들을 끌어이 수식을 다른 셀에 복사합니다. 이제 일치하는 값과 주석이 한 번에 반환됩니다. 스크린 샷을 참조하십시오.

주의 사항: 위의 공식에서 D2 해당 값을 반환하려는 조회 값입니다. A2 : B9 사용하려는 데이터 테이블입니다. 2 반환하려는 일치 값이 포함 된 열 번호입니다.


4. Vlookup에서 텍스트 및 실수 처리

예를 들어 데이터 범위가 있는데 원본 테이블의 ID 번호는 숫자 형식이고 텍스트로 저장된 조회 셀에서 일반 Vlookup 기능을 적용하면 아래 스크린 샷과 같이 # N / A 오류 결과가 표시됩니다. 표시됩니다. 이 경우 테이블의 조회 번호와 원래 번호의 데이터 형식이 다른 경우 어떻게 올바른 정보를 얻을 수 있습니까?

Vlookup 함수에서 텍스트와 실수를 처리하려면 다음 수식을 빈 셀에 적용한 다음 채우기 핸들을 아래로 끌어이 수식을 복사하면 아래 스크린 샷과 같이 올바른 결과를 얻을 수 있습니다.

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

배송 시 요청 사항:

  • 1. 위의 공식에서 D2 해당 값을 반환하려는 조회 값입니다. A2 : B8 사용하려는 데이터 테이블입니다. 2 반환하려는 일치 값이 포함 된 열 번호입니다.
  • 2.이 공식은 숫자가 어디에 있고 어디에 텍스트가 있는지 확실하지 않은 경우에도 잘 작동합니다.

VLOOKUP 샘플 파일 다운로드

Vlookup_basic_examples.xlsx

고급_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • 슈퍼 포뮬러 바 (여러 줄의 텍스트와 수식을 쉽게 편집 할 수 있습니다.) 레이아웃 읽기 (많은 수의 셀을 쉽게 읽고 편집합니다.) 필터링 된 범위에 붙여 넣기...
  • 셀 / 행 / 열 병합 및 데이터 보관; 셀 내용 분할; 중복 행과 합계 / 평균 결합... 중복 셀 방지; 범위 비교...
  • 중복 또는 고유 선택 행; 빈 행 선택 (모든 셀이 비어 있음); 슈퍼 찾기 및 퍼지 찾기 많은 통합 문서에서; 무작위 선택 ...
  • 정확한 사본 수식 참조를 변경하지 않고 여러 셀; 참조 자동 생성 여러 시트에; 글 머리 기호 삽입, 확인란 등 ...
  • 즐겨 찾기 및 빠른 수식 삽입, 범위, 차트 및 그림; 셀 암호화 암호로; 메일 링리스트 생성 이메일 보내기 ...
  • 텍스트 추출, 텍스트 추가, 위치 별 제거, 공간 제거; 페이징 부분합을 만들고 인쇄합니다. 셀 내용과 주석 간 변환...
  • 슈퍼 필터 (다른 시트에 필터 구성표 저장 및 적용) 고급 정렬 월 / 주 / 일, 빈도 등 특수 필터 굵은 기울임 꼴로 ...
  • 통합 문서와 워크 시트 결합; 키 열을 기반으로 테이블 병합; 데이터를 여러 시트로 분할; xls, xlsx 및 PDF 일괄 변환...
  • 피벗 테이블 그룹화 기준 주 번호, 요일 등 ... 잠금 해제되고 잠긴 셀 표시 다른 색상으로; 수식 / 이름이있는 셀 강조 표시...
kte 탭 201905
  • Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, Publisher, Access, Visio 및 Project.
  • 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 만듭니다.
  • 생산성이 50% 증가하고 매일 수백 번의 마우스 클릭이 줄어듭니다!
officetab 하단
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Anton · 1 months ago
    Здравствуйте, возможно ли сравнить любое значение из столбцов в таблице. Суть в том, что значения сделаны неправильно и есть слова "CO" "BE" как сделанные на английском так и на русском, чтобы каждый раз не делать замену их неудобно, т.к. файл обновляется постоянно. Необходимо чтобы Впр искал любой вариант из первого и второго столбца. 1 столбец русские символы, 2-ой английские.