이 예제는 여러 조건을 Vlookup하고, 특정 인스턴스 또는 모든 일치 항목을 반환하고, 동적 Vlookup을 수행하는 방법을 알려줍니다. 여러 시트 등.
Excel VLOOKUP의 강력한 기능을 활용하는 데 도움이되는 시리즈의 두 번째 부분입니다. 예제는이 함수의 작동 방식을 알고 있음을 의미합니다. 그렇지 않다면 Excel에서 VLOOKUP의 기본 사용부터 시작하는 것이 좋습니다.
계속 진행하기 전에 구문을 간략하게 알려 드리겠습니다.
이제 모든 사람이 같은 페이지에 있으므로 고급 VLOOKUP 수식 예제를 자세히 살펴 보겠습니다.
- Excel에서 여러 기준을 Vlookup하는 방법
- Vlookup 및 n 번째 일치 반환
- Vlookup 다중 값
- 행 및 열 값을 기반으로 Vlookup
- 중첩 된 Vlookup 수식
- VLOOKUP 및 INDIRECT로 여러 시트에서 데이터 가져 오기
여러 기준을 Vlookup하는 방법
Excel VLOOKUP 기능은 데이터베이스 검색시 매우 유용합니다. 그러나 중요한 기능이 없습니다. 구문은 조회 값을 하나만 허용합니다.하지만 여러 조건으로 조회하려는 경우 선택할 수있는 몇 가지 솔루션이 있습니다.
공식 1. tw 기반 VLOOKUP o 값
주문 목록이 있고 고객 이름과 제품이라는 두 가지 기준에 따라 수량을 찾으려고한다고 가정합니다. 복잡한 요인은 아래 표에 표시된 것처럼 각 고객이 여러 제품을 주문했다는 것입니다.
일반적인 VLOOKUP 공식은 여기에서 작동하지 않습니다. 지정한 단일 조회 값을 기반으로 처음 찾은 일치를 반환하기 때문입니다.
이를 극복하기 위해 도우미 열을 추가하고 두 조회 열 (고객 및 제품)의 값을 여기에 연결할 수 있습니다. 도우미 열은 Excel VLOOKUP에서 항상 조회 값을 검색하기 때문에 테이블 배열에서 가장 왼쪽에있는 열이어야합니다.
그러므로 테이블 왼쪽에 열을 추가하고 복사합니다. 해당 열에 대한 아래 공식. 그러면 도우미 열이 B와 C 열의 값으로 채워집니다 (가독성을 높이기 위해 공백 문자가 그 사이에 연결됨).
=B2&" "&C2
그런 다음 표준 VLOOKUP 수식을 사용하고 두 기준을 모두 lookup_value 인수에 공백으로 구분하여 입력합니다.
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
또는 별도의 셀 (이 경우 G1 및 G2)에 기준을 입력하고 해당 셀을 연결합니다.
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
테이블 배열에서 네 번째 인 열 D의 값을 반환하려면 col_index_num에 4를 사용합니다. range_lookup 인수는 정확히 일치하는 Vlookup을 위해 FALSE로 설정됩니다. 아래 스크린 샷은 결과를 보여줍니다.
조회 테이블이 다른 시트에있는 경우 VLOOKUP 수식에 시트의 이름을 포함합니다. 예 :
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
또는 수식을 더 쉽게 만들 수 있도록 조회 테이블 (예 : Orders)의 명명 된 범위를 만듭니다. 읽을 거리 :
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
자세한 내용은 Excel의 다른 시트에서 조회하는 방법을 참조하세요.
수식 2. 여러 조건이있는 Excel VLOOKUP
이론적으로 Vlookup에 위의 접근 방식을 두 가지 이상의 기준으로 사용할 수 있습니다. H 그러나 몇 가지주의 사항이 있습니다. 첫째, 조회 값은 255 자로 제한되고 둘째, 워크 시트의 디자인에서 도우미 열을 추가 할 수 없습니다.
다행히도 Microsoft Excel은 동일한 작업을 수행하는 여러 가지 방법을 제공하는 경우가 많습니다. 여러 기준을 Vlookup하려면 INDEX MATCH 조합 또는 최근 Office 365에 도입 된 XLOOKUP 함수를 사용할 수 있습니다.
예를 들어 3 가지 다른 값 (날짜, 고객 이름 및 제품)을 기준으로 조회하려면, 다음 수식 중 하나를 사용하세요.
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
위치 :
수식에 대한 자세한 설명은 다음을 참조하세요.
- 여러 기준이있는 XLOOKUP
- 여러 기준이있는 INDEX MATCH 수식
VLOOKUP을 사용하여 두 번째, 세 번째 또는 n 번째 일치 항목을 얻는 방법
이미 알고 있듯이 Excel VLOOKUP은 일치하는 값을 하나만 가져올 수 있으며보다 정확하게는 처음 찾은 일치 항목을 반환합니다. 그러나 조회 배열에 여러 일치 항목이 있고 두 번째 또는 세 번째 인스턴스를 얻으려면 어떻게해야합니까? 작업은 매우 복잡하게 들리지만 해결책은 존재합니다!
공식 1. Vlookup N 번째 인스턴스
한 열에 고객 이름이 있고 다른 열에 고객이 구매 한 제품이 있고 주어진 고객이 구매 한 두 번째 또는 세 번째 제품을 찾고 있습니다.
가장 간단한 방법은 첫 번째 예에서했던 것처럼 테이블 왼쪽에 도우미 열을 추가하는 것입니다. 그러나 이번에는 “John Doe1”, “John Doe2″등과 같은 고객 이름 및 발생 번호로 채워집니다.
발생을 가져 오려면 혼합 범위 참조 (the 첫 번째 참조는 절대적이고 두 번째 참조는 $ B $ 2 : B2와 같이 상대적입니다). 수식이 복사 된 셀의 위치에 따라 상대 참조가 변경되므로 3 행에서는 $ B $ 2 : B3, 4 행에서는 $ B $ 2 : B4 등이됩니다.
고객 이름 (B2)과 연결된 공식은 다음 형식을 취합니다.
=B2&COUNTIF($B$2:B2, B2)
위의 공식은 A2로 이동합니다. 그런 다음 필요한만큼 많은 셀에 복사합니다.
그 후 별도의 셀 (F1 및 F2)에 대상 이름과 발생 번호를 입력하고 아래 공식을 사용하여 특정 발생을 Vlookup합니다.
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
공식 2. Vlookup 두 번째 발생
조회 값의 두 번째 인스턴스를 찾고 있다면 도우미 열 없이도 할 수 있습니다. 대신 INDIRECT 함수를 MATCH와 함께 사용하여 동적으로 테이블 배열을 만듭니다.
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
위치 :
- E1은 조회 값입니다.
- A2 : A11은 조회 범위입니다.
- B11은 조회 테이블의 마지막 (오른쪽 아래) 셀입니다.
위의 공식은 조회 테이블의 데이터 셀이 2 행에서 시작하는 특정 경우를 위해 작성되었습니다. 테이블이 시트 중간에 다음 범용 공식을 사용합니다. 여기서 A1은 열 헤더가 포함 된 조회 테이블의 왼쪽 상단 셀입니다.
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
이 공식의 작동 원리
다음은 동적 vlookup 범위를 생성하는 공식의 핵심 부분입니다.
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
정확히 일치하도록 구성된 MATCH 함수 (마지막 인수의 0)는 대상 이름 (E1)을 이름 목록 (A2 : A11)과 비교하고 처음 찾은 일치 위치 (3)를 반환합니다. o에서 ur 사건. 이 숫자는 vlookup 범위의 시작 행 좌표로 사용되므로 여기에 2를 추가합니다 (첫 번째 인스턴스를 제외하려면 +1을, 열 머리글이있는 행 1을 제외하려면 +1). 또는 1 + ROW (A1)를 사용하여 헤더 행의 위치 (이 경우 A1)에 따라 필요한 조정을 자동으로 계산할 수 있습니다.
결과적으로 다음 텍스트 문자열을 얻습니다. INDIRECT가 범위 참조로 변환 :
INDIRECT("A"&5&":B11") -> A5:B11
이 범위는 VLOOKUP의 table_array 인수로 이동하여 행에서 검색을 시작합니다. 5, 조회 값의 첫 번째 인스턴스 제외 :
VLOOKUP(E1, A5:B11, 2, FALSE)
Excel에서 여러 값을 조회하고 반환하는 방법
Excel VLOOKUP 함수는 일치 항목을 하나만 반환하도록 설계되었습니다. 여러 인스턴스를 Vlookup하는 방법이 있습니까? 예, 쉽지는 않지만 있습니다. 이를 위해서는 INDEX, SMALL 및 ROW와 같은 여러 함수를 조합하여 사용해야합니다. 배열 수식입니다.
예를 들어 아래는 조회 범위 B2 : B16에서 조회 값 F2의 모든 항목을 찾아 반환 할 수 있습니다. C 열에서 여러 일치 항목 :
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
워크 시트에 수식을 입력하는 방법에는 두 가지가 있습니다.
- 첫 번째 셀에 수식을 입력하고 Ctrl + Shift + Enter를 누른 다음 더 많은 셀로 드래그합니다.
- 단일 열에서 인접한 여러 셀을 선택합니다 (F1 : F11 in 아래 스크린 샷 참조) 수식을 입력하고 Ctrl + Shift + Enter를 눌러 완료합니다.
어느 쪽이든 수식을 입력하는 셀의 수는 다음보다 크거나 같아야합니다. 가능한 최대 일치 수입니다.
수식 논리 및 더 많은 예에 대한 자세한 설명은 Excel에서 여러 값을 VLOOKUP하는 방법을 참조하세요. .
행 및 열에서 Vlookup (양방향 조회)하는 방법
양방향 조회 (행렬 조회 또는 2 차원 조회라고도 함)는 특정 행과 열의 교차점에서 값을 조회하는 멋진 단어입니다.Excel에서 2 차원 조회를 수행하는 몇 가지 다른 방법이 있지만이 자습서의 초점은 VLOOKUP 함수이므로 자연스럽게 사용합니다.
이 예제에서는 월별 매출이있는 표 아래에 있고 VLOOKUP 수식을 사용하여 특정 월의 특정 항목에 대한 매출 수치를 검색합니다.
항목 이름은 A2 : A9, 월 이름은 B1 : F1, 대상 항목 I1 및 I2의 목표 월에서 공식은 다음과 같습니다.
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
이 수식의 작동 원리
수식의 핵심은 I1의 조회 값과 정확히 일치하는 항목을 검색하는 표준 VLOOKUP 함수입니다. 특정 월의 매출이 정확히 포함 된 열에서는 col_index_num 인수에 열 번호를 직접 제공 할 수 없습니다. 해당 열을 찾으려면 다음 MATCH 함수를 사용합니다.
MATCH(I2, A1:F1, 0)
영어로 번역 된 공식은 다음과 같습니다. A1 : F1의 I2 값을 반환하고 배열에서 상대 위치를 반환합니다. 세 번째 인수에 0을 제공하면 MATCH에 조회 값과 정확히 동일한 값을 찾도록 지시합니다 (VLOOKUP의 range_lookup 인수에 FALSE를 사용하는 것과 같습니다).
Mar가 4 번째 열에 있기 때문에 조회 배열에서 MATCH 함수는 VLOOKUP의 col_index_num 인수로 직접 이동하는 4를 반환합니다.
VLOOKUP(I1, A2:F9, 4, FALSE)
주의하십시오 월 이름은 B 열에서 시작하지만 조회 배열로 A1 : I1을 사용합니다. 이는 MATCH에서 반환 된 숫자가 VLOOKUP의 table_array에서 열의 위치와 일치하도록하기 위해 수행됩니다.
Excel에서 행렬 조회를 수행하는 방법에 대한 자세한 내용은 INDEX MATCH MATCH 및 2 차원 조회를위한 기타 공식을 참조하십시오.
Excel에서 다중 Vlookup을 수행하는 방법 (중첩 된 Vlookup)
때때로 기본 테이블과 룩업 테이블에 공통 열이 하나도 없어 두 테이블간에 Vlookup을 수행 할 수없는 경우가 있습니다. 그러나 찾고있는 정보가 포함되어 있지 않지만 기본 테이블이있는 하나의 공통 열과 조회 테이블이있는 다른 공통 열이있는 다른 테이블이 있습니다.
아래 이미지는 상황을 보여줍니다.
목표는 항목 ID를 기반으로 가격을 기본 테이블에 복사하는 것입니다. 문제는 가격이 포함 된 테이블에 항목 ID가 없다는 것입니다. 즉, 하나의 수식에서 두 개의 Vlookup을 수행해야합니다.
편의상 두 개의 명명 된 범위를 먼저 만들어 보겠습니다. :
- 참고 표 1의 이름은 제품 (D3 : E3)입니다.
- 참고 표 2의 이름은 가격 (G3 : H3)입니다.
테이블은 동일하거나 다른 워크 시트에있을 수 있습니다.
이제 중첩 된 Vlookup이라고하는 소위 double Vlookup을 수행합니다.
먼저 VLOOKUP을 만듭니다. 항목 ID (A3)를 기반으로 조회 테이블 1 (제품 이름)에서 제품 이름을 찾는 수식 :
=VLOOKUP(A3, Products, 2, FALSE)
다음으로 다른 VLOOKUP 함수의 lookup_value 인수에 위의 수식을 넣어 중첩 된 VLOOKUP에서 반환 된 제품 이름을 기반으로 룩업 테이블 2 (이름이 Price)에서 가격을 가져옵니다.
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
아래 스크린 샷은 작동중인 중첩 된 Vlookup 수식을 보여줍니다.
여러 항목을 Vlookup하는 방법 동적 시트
때로는 동일한 형식의 데이터가 여러 워크 시트로 분할 될 수 있습니다. 그리고 목표는 주어진 셀의 키 값에 따라 특정 시트에서 데이터를 가져 오는 것입니다.
이는 예에서 이해하기 더 쉬울 수 있습니다. 동일한 형식의 지역 판매 보고서가 몇 개 있고 특정 지역의 특정 제품에 대한 판매 수치를 얻으려고한다고 가정 해 보겠습니다.
이전 예에서와 같이 몇 가지 이름을 정의하는 것으로 시작합니다.
- CA 시트의 범위 A2 : B5의 이름은 CA_Sales입니다.
- 범위 A2 : FL 시트의 B5 이름은 FL_Sales입니다.
- KS 시트의 범위 A2 : B5 이름은 KS_Sales입니다.
보시다시피 이름이 지정된 모든 범위에는 공통 부품 (판매) 및 고유 부품 (CA, FL, KS). 우리가 구축 할 공식에 필수적이므로 유사한 방식으로 범위 이름을 지정하십시오.
공식 1 여러 시트에서 데이터를 동적으로 가져 오는 INDIRECT VLOOKUP
여러 시트에서 데이터를 검색하는 작업 인 경우 VLOOKUP INDIRECT 수식이 가장 적합한 솔루션입니다. 간단하고 이해하기 쉽습니다.
이 예에서는 요약 테이블을 다음과 같이 구성합니다.
- A2 및 A3에 관심있는 제품을 입력합니다. 이것이 우리의 조회 값입니다.
- B1, C1 및 D1에 명명 된 범위의 고유 한 부분을 입력합니다.
이제 고유 한 부분 (B1)을 포함하는 셀을 공통 부분 ( “_Sales”)과 연결하고 결과 문자열을 INDIRECT에 공급합니다.
INDIRECT(B$1&"_Sales")
INDIRECT 함수는 문자열을 Excel이 이해할 수있는 이름으로 변환하고이를 VLOOKUP의 table_array 인수에 넣습니다.
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
위의 공식은 B2로 이동 한 다음 오른쪽 아래로 복사합니다.
주의하십시오. 조회 값 ($ A2)에서 공식이 오른쪽에 복사 될 때 열이 고정 된 상태로 유지되도록 절대 셀 참조로 열 좌표를 잠갔습니다. B $ 1 참조에서는 열을 원하기 때문에 행을 잠갔습니다. 수식이 복사되는 열에 따라 변경을 조정하고 적절한 이름 부분을 INDIRECT에 제공합니다.
기본 테이블이 구성된 경우 다르게, 행의 조회 값과 colu에있는 범위 이름의 고유 한 부분 mn, 조회 값 (B $ 1)의 행 좌표와 이름 부분 ($ A2)의 열 좌표를 잠 가야합니다.
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
수식 2. 여러 시트를 조회하기위한 VLOOKUP 및 중첩 IF
두 개 또는 세 개의 조회 시트를 사용하면 중첩 된 IF 함수가있는 매우 간단한 VLOOKUP 수식을 사용하여 특정 셀의 키 값을 기반으로 올바른 시트를 선택할 수 있습니다.
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
여기서 $ A2는 조회 값 (항목 이름)이고 B $ 1은 키 값 (상태)입니다.
이 경우 반드시 이름을 정의 할 필요는 없으며 외부 참조를 사용하여 다른 시트 또는 통합 문서를 참조 할 수 있습니다.
자세한 수식 예는 Excel의 여러 시트에서 VLOOKUP하는 방법을 참조하세요. .
이것이 Excel에서 VLOOKUP을 사용하는 방법입니다. 읽어 주셔서 감사합니다. 다음 주에 블로그에서 뵙기를 바랍니다.
실습 워크 북 다운로드
고급 VLOOKUP 수식 예제 (.xlsx 파일)
- 초보자를위한 Excel VLOOKUP 자습서
- Excel에서 여러 일치 항목을 Vlookup하는 방법
- VLOOKUP & SUM 또는 SUMIF 함수를 사용하는 방법 Excel
- INDEX & Excel의 MATCH 함수-VLOOKUP에 대한 더 나은 대안
- Excel에서 대소 문자 구분 Vlookup을 수행하는 4 가지 방법
- Excel VLOOKUP이 작동하지 않음-N / A, NAME 및 VALUE 오류