Excel 에 정통한 사람 이라면 VLOOKUP 기능 에 매우 익숙할 것 입니다. VLOOKUP 함수 는 동일한 행 내에서 일치하는 일부 텍스트를 기반으로 다른 셀에서 값을 찾는 데 사용됩니다.

VLOOKUP 기능 을 처음 사용하는 경우 Excel에서 VLOOKUP을 사용하는 방법^{(how to use VLOOKUP in Excel)} 에 대한 이전 게시물을 확인할 수 있습니다 .

VLOOKUP 은 강력하지만 수식이 작동하기 위해 일치하는 참조 테이블을 구조화해야 하는 방식에 제한이 있습니다.

이 기사에서는 VLOOKUP 을 사용할 수 없는 한계를 보여주고 이 문제를 해결할 수 있는 INDEX-MATCH 라는 Excel 의 다른 기능을 소개 합니다.

INDEX MATCH 엑셀 예제

다음 예제 Excel 스프레드시트 를 사용하여 자동차 소유자 이름과 자동차 이름 목록이 있습니다. 이 예에서는  아래와 같이 여러 소유자 아래에 나열된 자동차 모델^{(Car Model)} 을 기반으로 자동차 ID 를 가져오려고 합니다.^{(Car ID)}

CarType 이라는 별도의 시트 에는 ID , 자동차 모델^{(Car Model)} 및 색상^(Color) 이 있는 간단한 자동차 데이터베이스가 있습니다  .

이 테이블 설정을 사용하면  검색하려는 데이터가 일치시키려는 항목( 자동차 모델^{(Car Model )} 필드) 의 오른쪽 열에 있는 경우에만 VLOOKUP 기능이 작동할 수 있습니다.^(VLOOKUP)

즉, 이 테이블 구조에서는 Car Model 을 기반으로 일치시키려고 하기 때문에 얻을 수 있는 정보는 Color 뿐입니다 ( ID  열은  Car Model 열의 왼쪽에 있으므로 ID 가 아님).^(ID)

VLOOKUP 을 사용하면 조회 값이 첫 번째 열에 나타나야 하고 조회 열이 오른쪽에 있어야 하기 때문입니다 . 이 예에서는 이러한 조건 중 어느 것도 충족되지 않습니다.

좋은 소식은 INDEX-MATCH  가 이를 달성하는 데 도움이 될 수 있다는 것입니다. 실제로 이것은 개별적으로 작동할 수 있는 두 개의 Excel 함수인 ^(Excel)INDEX 함수와 MATCH 함수를 결합하는 것입니다.

그러나 이 기사의 목적을 위해 우리는 VLOOKUP^(VLOOKUP) 의 기능을 복제하기 위한 목적으로 둘의 조합에 대해서만 이야기할 것입니다 .

공식은 처음에는 약간 길고 겁이 나는 것처럼 보일 수 있습니다. 그러나 여러 번 사용하면 구문을 마음에 새기게 됩니다.

이 예의 전체 공식은 다음과 같습니다.

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

각 섹션에 대한 분석은 다음과 같습니다.

=INDEX( – “=” 는 셀의 수식 시작을 나타내고 INDEX 는 사용 중인 ^(INDEX)Excel 함수 의 첫 번째 부분입니다 .

CarType!$A$2:$A$5 –  검색하려는 데이터가 포함된 CarType 시트의 열입니다 . 이 예에서는  각 자동차 모델 의 ^{(Car Model.)}ID 입니다.^(ID)

MATCH( – 우리가 사용 하는 Excel 함수 의 두 번째 부분입니다 .

B4 – 우리가 사용하는 검색 텍스트가 포함된 셀( 자동차 모델^{(Car Model)} ) .

CarType!$B$2:$B$5 –   검색 텍스트와 일치시키는 데 사용할 데이터가 있는 CarType 시트의 열입니다 .

0)) – 검색 텍스트가 일치하는 열의 텍스트와 정확히 일치해야 함을 나타냅니다(예: CarType!$B$2:$B$5 ). 정확히 일치하는 항목이 없으면 수식은 #N/A 를 반환합니다 .

참고: 이 함수 "))"의 끝에 이중 닫는 대괄호와 인수 사이의 쉼표를 기억하십시오.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

개인적으로 저는 VLOOKUP^(VLOOKUP) 에서 벗어나 VLOOKUP 보다 더 많은 작업을 수행할 수 있는 INDEX-MATCH를 사용합니다 .

INDEX-MATCH 함수 에는 VLOOKUP 에 비해 다른 이점도 있습니다 .

1. 더 빠른 계산^{(Faster Calculations)}

많은 VLOOKUP^(VLOOKUP) 함수 로 인해 계산 자체에 시간이 오래 걸릴 수 있는 대규모 데이터 세트로 작업할 때 이러한 모든 수식을 INDEX-MATCH 로 바꾸면^{(INDEX-MATCH)} 전체 계산이 더 빠르게 계산됩니다.

1. 상대 열을 계산할 필요가 없습니다.^{(No Need to Count Relative Columns)}

참조 테이블에 열 C^(C) 에서 검색하려는 키 텍스트가 있고 가져와야 하는 데이터가 열 AQ 에 있는 경우 ^{( AQ)}VLOOKUP 을 사용할 때 열 C와 열 AQ 사이에 얼마나 많은 열이 있는지 알고/계산해야 합니다. .

INDEX-MATCH 함수를 사용하면  데이터를 가져와야 하는 인덱스 열(즉, 열 AQ)을 직접 선택하고 일치시킬 열(즉, 열 C)을 선택할 수 있습니다.

1. 더 복잡해 보인다^{(It Looks More Complicated)}

VLOOKUP 은 오늘날 매우 일반적이지만 INDEX-MATCH 기능을 함께 사용하는 것에 대해 아는 사람은 많지 않습니다.

INDEX-MATCH 함수 의 긴 문자열은 복잡하고 고급 Excel 함수 를 처리하는 전문가처럼 보이도록 도와줍니다 . 즐기다!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of yoυ who are well-versed in Excel, you are most likely very familiar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• VLOOKUP과 같은 Excel 수식에서 #N/A 오류를 수정하는 방법

• Excel에서 빈 줄을 삭제하는 방법

• Excel의 셀 말하기 기능을 사용하는 방법

• Word 문서에 Excel 워크시트를 삽입하는 방법

• Excel의 What-If 분석을 사용하는 방법

• Excel에서 날짜별로 정렬하는 방법

• Excel 워크시트에서 행 및 열 그룹화

• Excel에서 수식을 복사할 때 셀 참조 유지

• Excel에서 셀, 열 및 행을 병합하는 방법

• Excel에서 여러 연결 드롭다운 목록을 만드는 방법

• 키보드를 사용하여 Excel에서 행 높이 및 열 너비 변경

• Word 및 Excel에서 빠르게 숫자 철자 작성

• Excel에서 If 및 중첩 If 문을 사용하는 방법

• Excel에서 알파벳순으로 지정하는 방법

• Excel에서 IF 수식/문을 작성하는 방법

• Excel 워크시트에 CSV 또는 TSV를 삽입하는 방법

• Excel의 조옮김 기능을 사용하는 2가지 방법

• Excel에서 절대 참조를 사용하는 방법

• 2019년을 위한 10가지 Excel 팁 및 요령

• Excel에서 여러 행을 빠르게 삽입하는 방법