Excel에서 여러 값을 조회하는 방법: 단계별

Microsoft Excel에서 대규모 데이터 세트로 작업할 때 여러 조건과 일치하는 여러 값을 찾기가 어려울 수 있습니다. VLOOKUP과 같은 일부 기본 제공 함수는 원래 단일 값으로 작동하도록 설계되었습니다.

여러 Excel 함수를 결합하여 여러 값을 조회할 수 있습니다. 여기에는 VLOOKUP, INDEX, MATCH 및 IF 함수가 포함됩니다. 현재 버전의 Excel은 동적 배열을 사용할 수 있지만 이전 버전은 배열 수식을 사용합니다.

이 문서에서는 데이터에서 여러 값을 찾는 수식에서 이러한 함수를 사용하는 방법을 정확하게 보여줍니다.

갑시다!

목차

• 여러 값과 함께 VLOOKUP을 사용하는 방법
  • 배열 수식이란 무엇입니까?
  • VLOOKUP 예제
  • 최신 버전의 Excel
  • 이전 버전
  • 정확한 일치와 대략적인 일치
  • VLOOKUP에 대한 추가 정보
• INDEX 및 MATCH를 사용하여 여러 값을 조회하는 방법
  • Excel 365에서 INDEX와 MATCH를 함께 사용하는 방법
  • 이전 버전의 Excel 작업
• IF 함수를 사용하여 여러 값을 찾는 방법
  • 현재 Excel 버전
  • 이전 버전의 Excel
• 여러 기준에 SUMPRODUCT를 사용하는 방법
• 조회 기능의 3가지 일반적인 오류
  • 1. #N/A 오류
  • 2. #참조! 오류
  • 3. 순환 오류
• 조회 기능과 함께 IFERROR를 사용하는 방법
• 7 고급 조회 기술
  • 1. 상대 위치 조회
  • 2. 작은 기능
  • 3. 행 기능
  • 4. 필터 기능
  • 5. 도우미 열
  • 6. 동적 배열
  • 7. 파워 쿼리
• 마지막 생각들

여러 값과 함께 VLOOKUP을 사용하는 방법

VLOOKUP 함수는 종종 데이터 범위에서 단일 값을 찾는 데 사용됩니다 . 그러나 이 조회 수식을 사용하여 Excel에서 여러 일치 항목을 조회할 수도 있습니다.

기본적으로 찾은 첫 번째 일치 값만 반환합니다. 그러나 배열 수식을 사용하여 여러 값을 반환하도록 함수를 수정할 수 있습니다.

배열 수식이란 무엇입니까?

배열 수식은 데이터 배열에서 계산을 수행할 수 있는 수식입니다. 단일 값이 아닌 결과 배열을 반환할 수 있기 때문에 배열 수식이라고 합니다.

배열 수식을 만들 때 몇 가지 쉬운 단계가 있습니다.

1. 검색할 셀 범위를 선택합니다.

2. 수식 입력줄에 수식을 입력합니다.

3. Ctrl + Shift + Enter를 눌러 완료합니다.

배열 수식의 구문은 일반 수식의 구문과 비슷하지만 수식 주위에 중괄호 {}가 포함됩니다. 중괄호는 수식이 배열 수식이며 값 배열을 반환함을 나타냅니다.

이 문서의 예제에서는 배열 수식을 올바르게 사용하는 방법을 보여줍니다.

VLOOKUP 예제

이 예제에는 워크시트의 A 열에 5개의 항목이 있습니다.

• 사과

• 바나나

• 빵

• 당근

• 체리

작업은 이 목록에 사과, 바나나, 체리의 세 가지 특정 과일이 있는지 확인하는 것입니다.

최신 버전의 Excel

VLOOKUP 구문은 사용 중인 Excel 버전에 따라 다릅니다.

최신 버전( Excel 365 또는 Excel 2021 )은 동적 배열을 지원합니다. 이 기능을 사용하면 수식이 인접한 셀에 "분할"되는 여러 결과를 반환할 수 있습니다.

다음은 구문입니다(등호는 수식을 시작함).

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

• lookup_value: 조회하려는 값입니다.

• table_array: 검색하려는 전체 데이터 테이블입니다.

• col_index_num: 반환하려는 데이터가 포함된 table_array의 테이블 열 번호입니다.

• range_lookup: 선택 사항입니다. 정확한 일치 또는 대략적인 일치를 원하는지 여부를 지정합니다.

구체적인 예에서는 다음 공식을 사용합니다.

=VLOOKUP({"사과","바나나","체리"},A1:A5,1,FALSE)

셀 B1에 수식을 입력하면 결과가 셀 C1과 D1에 쏟아집니다. 이 그림은 작동 중인 예를 보여줍니다.

이전 버전

동적 배열을 지원하지 않는 이전 버전의 Excel(예: Excel 2019 이하)을 사용하는 경우 배열 수식에 약간 다른 접근 방식을 사용해야 합니다.

다음과 같이하세요:

1. 첫 번째 항목(예: 열 B)에 대한 결과를 표시하려는 셀을 클릭합니다.

2. 아직 Enter 키를 누르지 않고 다음 수식을 입력합니다. =VLOOKUP({“apple”,”banana”,”cherry”}, A1:A5, 1, FALSE)

3. Ctrl + Shift + Enter를 눌러 이것을 배열 수식으로 바꿉니다.

4. 셀 B1을 복사하여 아래 셀에 붙여넣습니다(또는 채우기 핸들 사용).

Ctrl + Shift + Enter를 사용하면 Excel에서 수식 주위에 중괄호를 추가합니다. 이는 배열 수식임을 나타냅니다.

정확한 일치와 대략적인 일치

기본적으로 VLOOKUP 함수는 대략적인 일치를 사용합니다. 즉, 셀 값이 정확히 일치하지 않더라도 찾을 수 있는 가장 가까운 일치 항목을 반환합니다.

정확한 일치를 수행하려면 range_lookup 인수를 FALSE로 설정할 수 있습니다.

대략적인 일치는 정렬된 숫자 값에서 가장 잘 작동한다는 점을 명심하십시오. 일반적으로 셀 값이 텍스트인 경우 적절하지 않습니다.

VLOOKUP에 대한 추가 정보

이 다재다능한 기능에 대해 자세히 알아보려면 다음 문서를 확인하세요.

• Excel에서 VLOOKUP을 사용하여 두 열을 비교하는 방법

• VLOOKUP으로 불일치를 찾는 방법

이제 VLOOKUP 함수로 설정되었으므로 다른 방식으로 수행할 수 있는 두 가지 다른 함수인 INDEX 및 MATCH를 살펴보겠습니다.

INDEX 및 MATCH를 사용하여 여러 값을 조회하는 방법

INDEX 및 MATCH 함수를 결합하여 여러 행에서 여러 값을 찾을 수 있습니다.

Excel의 INDEX 함수는 지정된 범위 내의 셀에 대한 값 또는 참조를 반환합니다.

=INDEX(배열, 행_번호, [열_번호])

• 배열: 값을 검색할 셀 범위입니다.

• row_num: 값을 반환할 배열 내의 행 번호입니다.

• column_num: (선택 사항) 값을 반환할 배열 내의 열 번호입니다. 생략하면 함수는 전체 행을 반환합니다.

Excel의 MATCH 함수는 지정된 범위 내에서 값의 위치를 ​​반환합니다.

=MATCH(lookup_value, lookup_array, [일치_유형])

• lookup_value: lookup_array 내에서 검색할 값입니다.

• lookup_array: lookup_value를 검색할 셀 범위.

• match_type: (선택 사항) 수행할 일치 유형입니다. 생략하면 함수가 정확한 일치를 수행합니다.

Excel 365에서 INDEX와 MATCH를 함께 사용하는 방법

INDEX와 MATCH를 함께 사용하여 Excel에서 여러 값을 조회하려면 배열 수식을 사용해야 합니다.

이전 샘플 데이터로 작업할 때 Excel 365의 수식은 다음과 같습니다.

=INDEX(A1:A5, MATCH({“apple”,”banana”,”cherry”}, A1:A5, 0))

The above example breaks down as this:

• INDEX: this returns the value of a cell in a specified range based on a given row and column number. In this case, it will return the value from the range A1:A5.

• A1:A5: This is the defined table range where you’re searching for the value and from which the result will be returned.

• MATCH: this searches for a specified item in a range of cells and returns the relative position of that item in the range.

• {“apple”,”banana”,”cherry”}: this is the array constant containing the values you want to look up.

• A1:A5: this is the range where MATCH will search for the values from the array constant.

• 0: this is the match type for MATCH function. In this case, it’s 0, which means you’re looking for an exact match instead of a close match.

 This picture shows the formula in action:

Working with Older Versions of Excel

If you’re using an older Excel file that doesn’t support dynamic arrays (e.g., Excel 2019 or earlier), you’ll need to use a different approach.

Because older versions don’t support the formulas “spilling” into adjacent cells, you will need to break out the usage into three separate formulas.

Follow these steps:

1. Click on the cell where you want the result for the first item (e.g., cell B1)

2. Type the below formula:

3. =INDEX(A1:A5, MATCH(“apple”, A1:A5, 0))

4. Press Enter to execute the formula.

5. Type this formula in cell B2: =INDEX(A1:A5, MATCH(“banana”, A1:A5, 0))

6. Type this formula in cell B3: =INDEX(A1:A5, MATCH(“cherry”, A1:A5, 0))

This picture shows cell reference B3:

INDEX and MATCH functions aren’t the only ones that can enable you to find multiple values. In the next section, we look at how you can use the IF function as an alternative.

How to Use the IF Function to Find Multiple Values

Another way to lookup multiple cell values based on certain criteria is to use the IF function with other functions.

The IF function allows you to test multiple conditions and return different results depending on the outcome of those tests.

For example, let’s say you have a table of sales data with columns for Product and Sales. You want to lookup and total the sales amount for two of the three products.

Current Versions of Excel

To find the sum of the Sales column where the product is either “Apple” or “Banana” using the IF function, you can use an array formula with IF, SUM, and OR functions.

Assuming your data starts in cell A1, use the following formula:

=SUM(IF((A2:A4=”Apple”)+(A2:A4=”Banana”), B2:B4, 0))

The section (A2:A4=”Apple”)+(A2:A4=”Banana”) creates an array that has a value of 1 if the cell in range A2:A4 contains “Apple” or “Banana”, and 0 otherwise.

The IF statement checks each element of the array argument. If the value is 1 (i.e., the product is either “Apple” or “Banana”), it takes the corresponding value in the Sales column (range B2:B4); otherwise, it takes 0.

The SUM function adds up the values from the IF function, effectively summing the sales values for both “Apple” and “Banana”.

This picture shows the formula in action on the search range:

Older Versions of Excel

In Excel 2019 or earlier, you need to use an array formula. Follow these steps:

1. Type the formula but do not hit Enter.

2. Press Ctrl + Shift + Enter to make it an array formula.

Excel will add curly brackets {} around the formula, indicating it’s an array formula.

Next, we look at how you could use SUMPRODUCT to lookup several values based on your criteria. Let’s go!

How to Use SUMPRODUCT for Multiple Criteria

The SUMPRODUCT function also allows you to lookup multiple values based on multiple criteria.

Because it does not require the use of an array formula, the syntax is the same regardless of the version of Excel.

Using the same data as in the previous example, the formula looks like this:

=SUMPRODUCT((A2:A4=”Apple”)+(A2:A4=”Banana”), B2:B4)

The section (A2:A4=”Apple”)+(A2:A4=”Banana”) creates an array that has a value of 1 if the cell in range A2:A4 contains “Apple” or “Banana”, and 0 otherwise.

SUMPRODUCT 함수는 배열의 요소를 Sales 열(범위 B2:B4)의 해당 요소와 곱합니다. 그런 다음 결과 값을 합산하여 "Apple"과 "Banana"의 판매 값을 효과적으로 합산합니다.

아래 수식은 실제 작동을 보여줍니다.

Excel 함수는 예상대로 작동할 때 놀랍지만 때때로 오류가 발생할 수 있습니다. 다음 섹션에서는 몇 가지 일반적인 오류와 이를 처리하는 방법을 다룹니다.

조회 기능의 3가지 일반적인 오류

조회 기능은 때로 실망스럽고 문제 해결에 시간이 많이 걸리는 오류를 반환할 수 있습니다. 발생하는 가장 일반적인 세 ​​가지 오류는 다음과 같습니다.

1. #N/A 오류

2. #참조! 오류

3. 순환 오류

1. #N/A 오류

#N/A 오류는 조회 배열에서 조회 값을 찾을 수 없을 때 발생합니다.

이 오류가 발생하는 데는 다음과 같은 몇 가지 이유가 있습니다.

• 조회 값의 철자가 잘못되었거나 올바르지 않습니다.

• 조회 배열은 오름차순으로 정렬되지 않습니다.

• 조회 값이 데이터 세트에 없습니다.

조회 값이 데이터 세트에 없을 때 이는 유용한 정보입니다. 그러나 경험이 없는 Excel 사용자는 #N/A가 수식에 문제가 있음을 의미한다고 생각할 수 있습니다. 다음 섹션에서는 이를 보다 사용자 친화적으로 만드는 방법을 보여줍니다.

2. #참조! 오류

#REF! 조회 배열 또는 반환 배열이 삭제되거나 이동되면 오류가 발생합니다.

이 오류는 조회 기능에서 셀 참조를 업데이트하여 수정할 수 있습니다.

3. 순환 오류

복잡한 수식에서 함수를 결합하면 Excel에서 순환 참조가 있다고 알려줄 수 있습니다.

Excel에서 순환 참조를 찾기 위한 가이드를 사용하여 이러한 항목을 더 쉽게 조사할 수 있습니다 .

조회 기능과 함께 IFERROR를 사용하는 방법

IFERROR 함수는 조회 함수의 오류를 처리하는 데 유용한 도구입니다. 조회 함수가 오류를 반환하는 경우 반환할 값이나 수식을 지정할 수 있습니다.

IFERROR 함수의 구문은 다음과 같습니다.

=IFERROR(값, 값_if_오류)

· 값: 평가하려는 값 또는 공식.

· Value_if_error: 첫 번째 인수가 오류를 반환하는 경우 반환할 값 또는 공식.

예를 들어 테이블에서 여러 값을 조회하는 VLOOKUP 함수가 있다고 가정해 보겠습니다. 아래 그림에서 값 중 하나가 검색된 데이터 범위에 존재하지 않습니다.

보시다시피 #N/A 오류가 표시되어 경험이 없는 Excel 사용자에게 혼란을 줄 수 있습니다.

대신 IFERROR를 사용하여 빈 셀을 표시하거나 다음 구문으로 "찾을 수 없음"이라는 메시지를 표시할 수 있습니다.

=IFERROR(VLOOKUP(lookup_value, table_array, column_index, FALSE), "찾을 수 없음")

이 예에서 VLOOKUP 함수가 오류를 반환하면 IFERROR 함수는 "찾을 수 없음"이라는 메시지를 대신 반환합니다.

이 그림은 작동 중인 공식을 보여줍니다. 열 B에는 누락된 값이 있고 열 C와 열 D는 일치 항목을 찾았습니다.

우리는 지금까지 많은 부분을 다루었으며 마침내 조회를 위한 고급 기술을 배울 준비가 되었습니다. 이것이 다음 섹션의 주제입니다.

7 고급 조회 기술

Excel에서 여러 값을 조회하는 것은 특히 대규모 데이터 세트를 처리할 때 어려운 작업일 수 있습니다. 느린 처리로 인해 성능 문제가 발생할 수 있습니다.

프로세스를 보다 쉽고 효율적으로 만들기 위해 사용할 수 있는 7가지 고급 조회 기술이 있습니다.

• 상대 위치 조회

• 작은 기능

• 행 함수

• 필터 기능

• 도우미 열

• 동적 배열

• 파워 쿼리

1. 상대 위치 조회

Excel에서 여러 값을 조회하는 가장 간단한 방법 중 하나는 상대 위치 조회를 사용하는 것입니다. 여기에는 원하는 값을 찾기 위해 지정된 셀에서 행 및 열 오프셋을 지정하는 작업이 포함됩니다.

예를 들어, 데이터 테이블이 있고 주어진 셀의 오른쪽으로 2행 3열에 있는 값을 조회하려는 경우 다음 수식을 사용할 수 있습니다.

=OFFSET(셀, 2, 3)

2. 작은 기능

다중 값 조회를 위한 또 다른 유용한 기술은 SMALL 함수를 사용하는 것입니다. 이 함수는 셀 범위에서 n번째로 작은 값을 반환합니다.

이 함수를 INDEX 및 MATCH와 같은 다른 조회 함수와 결합하면 특정 기준에 따라 여러 값을 조회할 수 있습니다.

예를 들어 다음 수식은 특정 조건을 충족하는 셀 범위에서 두 번째로 작은 값을 조회합니다.

=INDEX(데이터, MATCH(SMALL(IF(기준, 범위), 2), 범위, 0))

3. 행 기능

ROW 함수는 Excel의 여러 값 조회에도 사용할 수 있습니다. 이 함수는 데이터 테이블의 셀을 참조하는 데 사용할 수 있는 지정된 셀의 행 번호를 반환합니다.

예를 들어 다음 수식은 고유 식별자를 기반으로 테이블에서 값을 조회합니다.

=INDEX(data, MATCH(unique_identifier, data[unique_identifier], 0), column_index_number)

4. 필터 기능

이전 버전의 Excel에서는 FILTER 기능을 사용할 수 없습니다.

Excel 365에서는 이를 사용하여 특정 기준에 따라 셀 범위를 필터링하고 해당 기준을 충족하는 값만 반환할 수 있습니다. 다음은 구문과 세 가지 인수입니다.

=FILTER(배열, 포함, [if_empty])

• 배열: 필터링하려는 특정 데이터입니다.

• include: include 인수는 배열에 적용할 기준 또는 조건입니다.

• [if_empty](선택 사항): 포함 인수에 지정된 기준을 충족하는 행이나 열이 없는 경우 반환할 값입니다.

예를 들어 다음 수식은 예제 데이터에서 작동하여 첫 번째 열의 세 항목 중 두 항목에 대한 일치 항목을 찾고 두 번째 열에서 해당 값을 합산합니다.

=SUM(FILTER(B2:B4, (A2:A4=”사과”)+(A2:A4=”바나나”)))

이 그림은 일치하는 행 수와 서로 다른 값의 합계를 보여줍니다.

5. 도우미 열

도우미 열을 사용하여 VLOOKUP과 같은 함수 내에서 여러 필드를 결합할 수 있습니다.

별도의 시트에서 이름과 성을 사용하여 작업한다고 가정합니다. 최종 수식 내에서 참조되는 도우미 열에 연결할 수 있습니다.

6. 동적 배열

이전 예제에서 배운 것처럼 Microsoft 365 사용자는 Excel에서 여러 값 조회를 위해 동적 배열을 활용할 수 있습니다.

동적 배열을 사용하면 단일 수식에서 여러 값을 반환할 수 있으므로 많은 양의 데이터를 쉽게 조회할 수 있습니다.

7. 파워 쿼리

파워 쿼리는 여러 기준에 따라 값을 반환하는 데 사용할 수 있는 Excel의 강력한 도구입니다.

예를 들어 이 비디오는 스프레드시트에서 지저분한 데이터를 찾아 정리합니다.

마지막 생각들

이것으로 Excel에서 여러 값을 조회하는 기술에 대한 심층 분석을 마무리합니다! VLOOKUP, INDEX, MATCH 및 배열 수식을 마스터하고 나면 뜨거운 칼로 버터를 자르듯 복잡한 데이터 세트를 빠르게 살펴보는 자신을 발견하게 될 것입니다.

핵심은 각 수식의 구문과 논리를 이해하는 것입니다. 이러한 수식은 복잡할 수 있으므로 중요한 데이터 분석에 의존하기 전에 시간을 들여 수식을 철저하게 테스트하는 것이 중요합니다.

Excel은 강력한 도구이지만 사용자만큼 유용합니다. 따라서 이러한 기술을 계속 연마하고 실험을 계속하면 곧 다중 값 조회의 마스터가 될 것입니다. 다음 시간까지 계속해서 그 숫자를 계산하고 Excel이 당신을 위해 힘든 일을 하도록 만드세요!