Vlookup 수식은 수식의 테이블 배열이 변경되지 않는 경우에만 작동하지만 테이블에 새 열이 삽입되거나 열이 삭제되면 수식이 잘못된 결과를 제공하거나 오류를 반영하여 수식에 오류가 없도록합니다. 이러한 동적 상황에서는 일치 함수를 사용하여 실제로 데이터의 인덱스를 일치시키고 실제 결과를 반환합니다.
VLOOKUP과 Match 결합
vlookup 수식은 지정된 열 인덱스에서 동일한 값을 검색하고 반환하는 데 사용되는 가장 일반적으로 사용되는 함수 또는 첫 번째 열에서 일치하는 값을 참조하여 다른 열 인덱스의 값을 반환합니다. vlookup을 사용하는 동안 직면 한 주요 문제는 지정할 열 인덱스가 정적이고 동적 기능이 없다는 것입니다. 특히 참조 열 인덱스를 수동으로 변경해야하는 여러 기준에 대해 작업 할 때. 따라서 VLOOKUP 수식에서 자주 변경되는 열 인덱스를 더 잘 파악하거나 제어 할 수 있도록 "MATCH"수식을 사용하여 이러한 요구를 충족합니다.
VLookup and Match Formula
# 1 – VLOOKUP 공식
Excel의 VLOOKUP 함수 공식
여기에 입력 할 모든 인수는 필수입니다.
- Lookup_value – 여기에 참조 셀 또는 큰 따옴표가있는 텍스트를 입력하여 열 범위에서 식별해야합니다.
- 테이블 배열 – 이 인수를 사용하려면 Lookup_value를 검색해야하고 검색 할 데이터가 특정 열 범위에있는 위치에 테이블 범위를 입력해야합니다.
- Col_index_num – 이 인수에서 첫 번째 열에서 검색된 값과 동일한 위치에서 해당 값을 가져와야 하는 첫 번째 참조 열의 열 인덱스 번호 또는 열 개수를 입력해야합니다.
- [Range_lookup] – 이 인수는 두 가지 옵션을 제공합니다.
- TRUE – 대략적인 일치 :-인수는 TRUE 또는 숫자 "1"로 입력 할 수 있으며 참조 열 또는 첫 번째 열에 해당하는 대략적인 일치를 반환합니다. 또한 테이블 배열의 첫 번째 열에있는 값은 오름차순으로 정렬되어야합니다.
- FALSE – 정확히 일치 :-여기에 입력 할 인수는 FALSE 또는 숫자 "0"일 수 있습니다. 이 옵션은 첫 번째 열 범위의 위치에서 식별되는 값과 정확히 일치하는 값만 반환합니다. 첫 번째 열의 값을 검색하지 못하면 "# N / A"오류 메시지가 반환됩니다.
# 2 – 공식 일치
Match 함수는 주어진 테이블 배열에 대해 입력 된 값의 셀 위치를 반환합니다.
구문 내의 모든 인수는 필수입니다.
- Lookup_value – 여기서 입력 된 인수는 값의 셀 참조이거나 셀 위치를 가져와야하는 큰 따옴표가있는 텍스트 문자열 일 수 있습니다.
- Lookup_array – 값 또는 셀 내용을 식별하려는 테이블의 배열 범위를 입력해야합니다.
- [일치 유형] –이 인수는 아래에 설명 된대로 세 가지 옵션을 제공합니다.
- "1-Less than" – 여기서 입력 할 인수는 숫자 "1"이며 조회 값보다 작거나 같은 값을 반환합니다. 또한 조회 배열은 오름차순으로 정렬되어야합니다.
- "0- 정확히 일치"– 여기에서 입력 할 인수는 숫자 "0"이어야합니다. 이 옵션은 일치하는 조회 값의 정확한 위치를 반환합니다. 그러나 조회 배열은 임의의 순서 일 수 있습니다.
- "-1-Greater than"– 입력 할 인수는 숫자 "-1"이어야합니다. 세 번째 옵션은 조회 값보다 크거나 같은 가장 작은 값을 찾습니다. 여기서 조회 배열의 순서는 내림차순으로 배치해야합니다.
# 3 – MATCH Formula를 사용한 VLOOKUP
= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])
Excel에서 일치 수식으로 VLOOKUP을 사용하는 방법은 무엇입니까?
아래의 예는 조합 할 때 vlookup 및 match 수식의 기능을 이해하는 데 도움이됩니다.
이 VLookup with Match Excel 템플릿을 여기에서 다운로드 할 수 있습니다 – Match Excel 템플릿을 사용한 VLookup구매할 차량의 사양을 설명하는 아래 데이터 표를 고려하십시오.
vlookup 및 match 함수에 대한 결합 된 함수의 명확성을 얻기 위해 개별 수식이 작동하는 방식을 이해 한 다음 함께 결합 할 때 vlookup 일치 결과에 도달합니다.
단계 # 1 – 개별 수준에서 vlookup 공식을 적용하여 결과를 얻습니다.
출력은 다음과 같습니다.
여기서 룩업 값은 모델 "E"인 $ B9를 참조하고 룩업 배열은 절대 값 "$"가있는 데이터 테이블의 범위로 주어지며, 열 인덱스는 카운트 인 열 "4"를 참조합니다. 열 "유형"과 범위 조회는 정확히 일치합니다.
따라서 "Fuel" 열의 값을 반환하기 위해 다음 공식이 적용됩니다 .
출력은 다음과 같습니다.
여기에서 조회 값 및 lookup_array에 절대 문자열 "$"가 적용된 조회 값은 수식이 다른 셀에 복사되는 경우에도 참조 셀을 수정하는 데 도움이됩니다. "Fuel"열에서 데이터를 검색하는 데 필요한 값이 변경되면 열 인덱스를 "5"로 변경해야합니다.
2 단계 – 이제 Match 공식을 적용하여 주어진 조회 값에 대한 위치를 검색합니다.
출력은 다음과 같습니다.
위의 스크린 샷에서 볼 수 있듯이 여기에서는 테이블 배열에서 열 위치를 검색하려고합니다. 이 경우 가져올 열 번호는 "Type"열인 C8 셀이라고하며 검색 할 조회 범위는 열 머리글 범위로 지정되며 일치 유형은 " 0”.
따라서 아래 표는 "Fuel"열의 위치에 대해 원하는 결과를 제공합니다.
이제 여기에서 검색 할 열이 D8 셀에 지정되고 원하는 열 인덱스가 "5"로 반환됩니다.
단계 # 3 – 이제 vlookup 함수 내에서 Match 수식을 사용하여 식별 된 열 위치에서 값을 가져옵니다.
출력은 다음과 같습니다.
위의 공식에서 일치 함수는 vlookup 함수의 열 인덱스 매개 변수 대신 배치됩니다. 여기서 match 함수는 조회 값 참조 셀 "C8" 을 식별 하고 주어진 테이블 배열을 통해 열 번호를 반환합니다. 이 열 위치는 vlookup 함수의 열 인덱스 인수에 대한 입력으로 사용됩니다. 결과적으로 결과 열 인덱스 번호에서 반환되는 값을 식별하는 데 vlookup이 도움이 될까요?
마찬가지로 "Fuel"열에 대해서도 일치 수식이있는 vlookup을 적용했습니다.
출력은 다음과 같습니다.
따라서 다른 열 "Type"및 "Fuel"에도이 조합 함수를 적용 할 수 있습니다.
기억해야 할 사항
- VLOOKUP은 가장 왼쪽에있는 조회 값에만 적용 할 수 있습니다. 데이터 테이블의 오른쪽에 검색 할 값이 있으면 "# N / A"오류 값이 반환됩니다.
- 두 번째 인수에 입력 된 table_array의 범위는 절대 셀 참조 "$"여야합니다. 이렇게하면 조회 수식을 다른 셀에 적용 할 때 고정 테이블 배열 범위가 유지됩니다. 그렇지 않으면 테이블 배열 범위에 대한 참조 셀이 다음 셀로 이동합니다. 참고.
- 조회 값에 입력 된 값은 테이블 배열의 첫 번째 열에있는 가장 작은 값보다 작아서는 안됩니다. 그렇지 않으면 함수가 "# N / A"오류 값을 반환합니다.
- 마지막 인수에 "TRUE"또는 "1"에 대한 대략적인 일치를 적용하기 전에 항상 테이블 배열을 오름차순으로 정렬해야합니다.
- match 함수는 vlookup 테이블 배열에있는 값의 위치 만 반환하고 값을 반환하지 않습니다.
- Match Function의 경우 테이블 배열에서 조회 값의 위치를 식별 할 수없는 경우 수식은 오류 값에 "# N / A"를 반환합니다.
- Vlookup 및 match 함수는 조회 값을 테이블 배열의 일치하는 텍스트 값과 일치시킬 때 대소 문자를 구분하지 않습니다.