Excel에서 VLOOKUP 함수는 대부분의 Excel 사용자에게 강력한 함수로, 데이터 범위의 가장 왼쪽에 있는 값을 찾고 아래 스크린 샷과 같이 지정한 열에서 동일한 행에 일치하는 값을 반환하는 데 사용됩니다. . 이 자습서에서는 Excel의 기본 및 고급 예제와 함께 VLOOKUP 함수를 사용하는 방법에 대해 설명합니다.
문서 맨 아래 셈플 3개의 파일 다운로드할 수 있습니다!
VLOOKUP 함수 소개 – 구문 및 인수
VLOOKUP 함수의 구문 :
= VLOOKUP ( lookup_value , table_array , col_index_num , [range_lookup])
인수 :
Lookup_value : 검색하려는 값입니다. table_array 범위의 첫 번째 열에 있어야합니다.
Table_array : 조회 값 열과 결과 값 열이있는 데이터 범위 또는 테이블입니다.
Col_index_num : 일치하는 값이 반환 될 열의 수입니다. 테이블 배열의 가장 왼쪽 열에서 1로 시작합니다.
Range_lookup :이 VLOOKUP 함수가 정확히 일치하는지 또는 대략적인 일치를 반환 할지를 결정하는 논리 값입니다.
- 대략적인 일치 – 1 / TRUE : 정확히 일치하는 항목이 없으면 수식에서 가장 가까운 일치 항목 (조회 값보다 작은 가장 큰 값)을 검색합니다. 이 경우 조회 열을 오름차순으로 정렬해야합니다.
= VLOOKUP (lookup_value, table_array, col_index, TRUE)
= VLOOKUP (lookup_value, table_array, col_index, 1) - 정확히 일치 – 0 / FALSE : 조회 값과 정확히 일치하는 값을 검색하는 데 사용됩니다. 정확히 일치하는 항목이 없으면 오류 값 # 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 은 일치하는 값이 반환되는 열 번호이며 FALSE 는 정확한 일치를 나타냅니다. .
- 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 일치 값
위의 공식이 힘들다면 여기에서 쉬운 도구 인 Kutools for Excel 을 소개하겠습니다. 두 값 사이 의 LOOKUP 기능을 사용하면 기억하지 않고 두 값 또는 날짜 사이의 특정 값 또는 날짜를 기반으로 해당 항목을 반환 할 수 있습니다. 모든 공식.
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, Datasheet! $ 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 는 조회 값을 나타냅니다.
[Product list.xlsx] Sheet1 은 데이터를 조회 할 통합 문서 및 워크 시트의 이름입니다 (통합 문서에 대한 참조는 대괄호로 묶이고 전체 통합 문서 + 시트는 작은 따옴표로 묶임).
A2 : B6 은 데이터를 검색하는 다른 통합 문서의 워크 시트에있는 데이터 범위입니다.
숫자 2 는 반환하려는 일치 데이터가 포함 된 열 번호입니다. - 2. 조회 통합 문서가 닫혀 있으면 다음 스크린 샷과 같이 조회 통합 문서의 전체 파일 경로가 수식에 표시됩니다.
9. Vlookup 및 0 또는 # N / A 오류 값 대신 공백 또는 특정 텍스트 반환
일반적으로 vlookup 함수를 적용하여 해당 값을 반환 할 때 일치하는 셀이 비어 있으면 0을 반환하고 일치하는 값을 찾을 수없는 경우 아래 스크린 샷과 같이 오류 # N / A 값이 표시됩니다.
고급 VLOOKUP 예제
1. Vlookup 기능을 사용한 양방향 조회 (행 및 열의 Vlookup)
때로는 2 차원 조회가 필요할 수 있습니다. 즉, 행과 열에서 동시에 Vlookup을 수행해야합니다. 다음과 같은 데이터 범위가 있고 지금은 지정된 분기에 특정 제품에 대한 값을 가져와야 할 수 있습니다. 이 섹션에서는 Excel에서이 작업을 처리하기위한 몇 가지 공식을 소개합니다.
공식 1 : VLOOKUP 및 MATCH 함수 사용
Excel에서는 VLOOKUP 및 MATCH 함수의 조합을 사용하여 양방향 조회를 수행 할 수 있습니다. 다음 수식을 빈 셀에 적용한 다음 Enter 키를 눌러 결과를 얻으십시오.
=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)
참고 : 위 공식에서 :
- H1 : 해당 값을 가져 오려는 열의 조회 값입니다.
- A2 : E6 : 행 헤더를 포함하는 데이터 범위;
- H2 : 해당 값을 기준으로하려는 행의 조회 값입니다.
- A1 : E1 : 열 헤더의 셀.
공식 2 : INDEX 및 MATCH 함수 사용
2 차원 조회를 수행하는 데 도움이되는 또 다른 공식이 있습니다. 아래 공식을 적용한 다음 Enter 키를 눌러 필요한 결과를 얻으십시오.
=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 : 범위 A2 : A12에서 G1의 기준을 검색하는 것을 의미합니다.
B2 : B12 = G2 : 범위 B2 : B12에서 G2의 기준을 검색하는 것을 의미합니다.
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 : 범위 A2 : A12에서 G1의 기준을 검색하는 것을 의미합니다.
B2 : B12 = G2 : 범위 B2 : B12에서 G2의 기준을 검색하는 것을 의미합니다.
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 . 결과를 출력하려는 빈 셀에 아래 수식을 복사하거나 입력하고 Enter 키를 눌러 첫 번째 값을 가져 오십시오.
=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 , ... : 조회 값과 반환 값이 존재하는 테이블;
- col : 일치하는 값을 반환 할 테이블의 열 번호.
- 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), "찾을 수 없음")))
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 및 첫 번째 일치 값 또는 모든 일치 값 합계
어쩌면 위의 수식을 당신은 기억하기 어려운,이 경우에는, 나는 편리한 기능을 추천합니다 - 조회 및 합계 의 Kutools을 Excel 용 ,이 기능을 사용하면 가능한 한 쉽게 결과를 얻을 수 있습니다.
행과 열 모두에서 일치하는 모든 값을 Vlookup 및 합계
열과 행을 모두 일치시켜야 할 때 값을 합산하려는 경우, 예를 들어 아래 스크린 샷과 같이 3 월에 제품 스웨터의 총 가치를 구할 수 있습니다.
다음 수식을 셀에 적용한 다음 Enter 키를 눌러 결과를 얻으십시오. 스크린 샷을 참조하십시오.
=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))
참고 : 위 공식에서 B2 : F9 는 합산 할 숫자 값의 데이터 범위입니다. B1 : F1 은 합산 할 조회 값이 포함 된 열 머리글입니다. 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 일치 값은 셀 서식 유지
1. 조회 값과 함께 셀 서식 (셀 색상, 글꼴 색상)을 가져 오는 Vlookup
우리 모두 알고 있듯이 일반 Vlookup 함수는 다른 데이터 범위에서 일치하는 값을 반환하는 데만 도움이 될 수 있지만 때로는 채우기 색상, 글꼴 색상, 글꼴 스타일과 같은 셀 서식과 함께 해당 값을 반환해야 할 수도 있습니다. 아래 스크린 샷과 같이. 이 섹션에서는 Excel에서 반환 된 값을 사용하여 셀 서식을 가져 오는 방법에 대해 설명합니다.
다음 단계를 수행하여 셀 서식과 함께 해당 값을 조회하고 반환하십시오.
1 . 워크 시트에 Vlookup 할 데이터가 포함되어 있고 시트 탭을 마우스 오른쪽 단추로 클릭 하고 상황에 맞는 메뉴에서 코드보기를 선택 합니다. 스크린 샷보기 :
2 . 열린 Microsoft Visual Basic for Applications 창에서 VBA 코드 아래를 코드 창에 복사하십시오.
VBA 코드 1 : 조회 값과 함께 셀 서식을 가져 오는 Vlookup
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Sub Worksheet_Change(ByVal Target As Range)<font></font> 'Updateby Extendoffice<font></font> Dim I As Long<font></font> Dim xKeys As Long<font></font> Dim xDicStr As String<font></font> On Error Resume Next<font></font> Application.ScreenUpdating = False<font></font> xKeys = UBound(xDic.Keys)<font></font> If xKeys >= 0 Then<font></font> For I = 0 To UBound(xDic.Keys)<font></font> xDicStr = xDic.Items(I)<font></font> If xDicStr <> "" Then<font></font> Range(xDic.Keys(I)).Interior.Color = _<font></font> Range(xDic.Items(I)).Interior.Color<font></font> Range(xDic.Keys(I)).Font.FontStyle = _<font></font> Range(xDic.Items(I)).Font.FontStyle<font></font> Range(xDic.Keys(I)).Font.Size = _<font></font> Range(xDic.Items(I)).Font.Size<font></font> Range(xDic.Keys(I)).Font.Color = _<font></font> Range(xDic.Items(I)).Font.Color<font></font> Range(xDic.Keys(I)).Font.Name = _<font></font> Range(xDic.Items(I)).Font.Name<font></font> Range(xDic.Keys(I)).Font.Underline = _<font></font> Range(xDic.Items(I)).Font.Underline<font></font> Else<font></font> Range(xDic.Keys(I)).Interior.Color = xlNone<font></font> End If<font></font> Next<font></font> Set xDic = Nothing<font></font> End If<font></font> Application.ScreenUpdating = True<font></font> End Sub<font></font> |
3 . 아직도의 응용 프로그램 용 Microsoft Visual Basic의 창을 클릭 삽입 > 모듈을 다음 모듈 창에 VBA 코드이 아래를 복사합니다.
VBA 코드 2 : 조회 값과 함께 셀 서식을 가져 오는 Vlookup
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Public xDic As New Dictionary<font></font> Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)<font></font> Dim xFindCell As Range<font></font> On Error Resume Next<font></font> Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)<font></font> If xFindCell Is Nothing Then<font></font> LookupKeepFormat = ""<font></font> xDic.Add Application.Caller.Address, ""<font></font> Else<font></font> LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value<font></font> xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address<font></font> End If<font></font> End Function<font></font> |
4 . 위 코드를 삽입 한 후 Microsoft Visual Basic for Applications 창 에서 도구 > 참조 를 클릭 합니다. 그런 다음 참조 – VBAProject 대화 상자 에서 Microsoft Script Runtime 확인란을 선택합니다 . 스크린 샷보기 :
5 . 그런 다음 클릭 확인 대화 상자를 닫은 다음 저장 후 닫기 코드 창, 이제 워크 시트를 다시 이동 한 다음이 공식 적용 : = 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 / yyyy 는 유지하려는 날짜 형식입니다.
3. Vlookup 및 셀 주석과 일치하는 값 반환
일치하는 셀 데이터뿐만 아니라 다음 스크린 샷과 같이 Excel에서도 셀 주석을 반환하기 위해 Vlookup을 시도한 적이 있습니까? 이 작업을 해결하기 위해 아래 사용자 정의 함수가 도움이 될 수 있습니다.
1 . 다운 상태에서 ALT + F11 열기 위해 열쇠를 응용 프로그램에 대한를 Visual Basic 창.
2 . 삽입 > 모듈을 클릭 한 다음 모듈 창에 다음 코드를 복사하여 붙여 넣습니다.
VBA 코드 : Vlookup 및 셀 주석과 일치하는 값 반환 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant<font></font> 'Updateby Extendoffice<font></font> Application.Volatile<font></font> Dim xRet As Variant 'could be an error<font></font> Dim xCell As Range<font></font> xRet = Application.Match(LookVal, FTable.Columns(1), FType)<font></font> If IsError(xRet) Then<font></font> VlookupComment = "Not Found"<font></font> Else<font></font> Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)<font></font> VlookupComment = xCell.Value<font></font> With Application.Caller<font></font> If Not .Comment Is Nothing Then<font></font> .Comment.Delete<font></font> End If<font></font> If Not xCell.Comment Is Nothing Then<font></font> .AddComment xCell.Comment.Text<font></font> End If<font></font> End With<font></font> End If<font></font> End Function<font></font> |
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.이 공식은 숫자가 어디에 있고 어디에 텍스트가 있는지 확실하지 않은 경우에도 잘 작동합니다.
'IT' 카테고리의 다른 글
| 연락처를 Android에서 IPhone으로 전송하는 방법 (0) | 2020.12.10 |
|---|---|
| Excel 시트에 주식 데이터 추가 하는 방법 (1) | 2020.12.10 |
| TV 구입 방법 : TV 구입전 알아야 할 사항 (0) | 2020.12.10 |
| 줌 Zoom 회의에서 그림 그리는 방법 윈도우, 모바일, 맥 (0) | 2020.12.09 |
| 안드로이드 스킨이란? 제조사 스킨 휴대폰 테마 (0) | 2020.12.09 |
댓글