여기에 있다면 VLOOKUP을 사용하여 데이터 세트에서 여러 값을 검색하려고한다는 의미입니다. 하지만 곧바로 설명하겠습니다. VLOOKUP은 여러 값을 반환 할 수 없습니다. 하지만 그것이 우리가 할 수 없다는 의미는 아닙니다. 첫 번째 값 대신 여러 값을 검색하는 조회를 수행 할 수 있습니다.

286

일반 공식

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}

배열 : 데이터를 가져올 범위입니다.

lookup_value : 필터링하려는 lookup_value입니다.

lookup_value_range : lookup_value를 필터링 할 범위입니다.

lookup_value 범위의 첫 번째 셀 : lookup_value 범위가 $ A $ 5 : $ A $ 100이면 $ A $ 5입니다.

중요 : 모든 것이`link : / excel-range-name-absolute-reference-in-excel [absolute referenced]`여야합니다. lookup_value는 요구 사항에 따라 상대적 일 수 있습니다.

배열 수식으로 입력하십시오. 수식을 작성한 후 CTRL + SHIFT + ENTER 키를 눌러 배열 수식으로 만듭니다.

여러 결과 검색의 예이 학생 데이터는 A2 : E14 범위에 있습니다. G1 셀에는 지역 값 드롭 다운이 있습니다. 중부, 동부, 북부, 남부 및 서부.

이제 G1에있는 지역에 관계없이 해당 지역의 모든 학생 목록이 H 열에 표시되어야합니다.

287

Excel에서 여러 값을 조회하기 위해 변수를 식별 해 보겠습니다.

배열 : $ C $ 2 : $ C $ 14 lookup_value : $ G $ 1 lookup_value_range : $ A $ 2 : $ A $ 14 lookup_value 범위의 첫 번째 셀 : $ A $ 2 위 데이터에 따르면 Excel에서 여러 값을 검색하는 공식은 다음과 같습니다.

{=INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1)))}

H2에서이 수식을 복사하고 Ctrl + Shift + Enter를 누릅니다. 이제 #NUM 오류가 발생할 때까지이 수식을 아래로 끕니다. #NUM은 해당 lookup_value와 일치하는 다른 값이 남아 있지 않고 목록의 끝임을 나타냅니다.

288

#NUM이 성가 시다면 수식 앞에`link : / logical-formulas-excel-iferror-function [IFERROR function]`을 추가하고 오류 대신 의미있는 정보를 표시 할 수 있습니다.

{=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1))),"--List Ends--")}

모든 항목이 표시 될 때 –List Ends—​가 표시됩니다.

이제 작동 원리를 이해하겠습니다.

수식이 복잡해 보이지만 아이디어는 간단합니다. 각 값의 색인 번호를 얻은 다음 Excel의 ‘link : / lookup-formulas-excel-index-function [INDEX function]’을 사용하여 값을 검색해야합니다.

따라서 주요 과제는 lookup_value의 인덱스 번호 배열을 가져 오는 것입니다. 색인 번호를 얻기 위해`link : / tips-if-condition-in-excel [IF] ʻand`link : / lookup-and-reference-excel-row-function [ROW]`

기능. 공식은 참으로 복잡합니다.

student 열에서 값을 가져 오려고하므로`link : / lookup-formulas-excel-index-function [INDEX function]`에 대한 배열은 $ C $ 2 : $ C $ 14입니다. 이제 우리는 G1의 값이 존재하는 $ A $ 2 : $ A $ 14 (lookup value)의 행 번호를 제공해야합니다 (지금은 G1에 중심이 있다고 가정 해 보겠습니다).

IF ($ G $ 1 = $ A $ 2 : $ A $ 14, ROW ($ A $ 2 : $ A $ 14) : 이제이 부분은 $ A $ 2 : $ A 범위에있는 셀의 G1 (central) 값을 계산하는 경우 행 번호를 반환합니다. $ 14는 FALSE를 반환합니다.이 예에서는

\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}를 반환합니다.

  • 이제 위의 배열에는 첫 번째 행 (1 : 1)의 행 번호가 포함되어 있고 배열 (A2 : A14)에서 시작하는 행이 필요합니다. 이렇게하려면 -ROW ($ A $ 2)를 사용합니다. IF 수식에서 +1. 이것은 배열이 시작되기 전에 행 수를 반환합니다.

이 예에서는 -1입니다. A3에서 시작하면 -2를 반환합니다. IF가 반환 한 배열의 각 숫자에서 숫자를 뺍니다. 따라서 마지막으로 IF ($ G $ 1 = $ A $ 2 : $ A $ 14, ROW ($ A $ 2 : $ A $ 14) -ROW ($ A $ 2) +1) \ {1; FALSE; FALSE; FALSE; FALSE; 6; 7; FALSE; FALSE; 10; FALSE; FALSE; FALSE}로 변환됩니다.

  • 다음으로이 배열은 ‘link : / statistics-formulas-excel-small-function [SMALL]`

함수.이 함수는 N 번째 작은 주어진 배열의 est 값. 이제`link : / statistical-formulas-excel-small-function [SMALL]`(\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}가 있습니다. , ROW (1 : 1)).

ROW (1 : 1)은 1을 반환합니다. 따라서 위의 함수는 배열에서 첫 번째로 작은 값인 2를 반환합니다.

이 수식을 아래 셀에 복사하면 ROW (1 : 1)은 ROW (2 : 2)가되고 배열에서 두 번째로 작은 값인 7을 반환합니다. 이렇게하면 함수가 처음 찾은 값을 먼저 반환 할 수 있습니다. 그러나 마지막으로 찾은 값을 먼저 얻으려면 SMALL 함수 대신 LARGE 함수를 사용하십시오.

이제 함수 위에 반환 된 값을 사용하여 INDEX 함수는 범위에서 일치하는 각 값을 쉽게 반환합니다.

예, 여러분은 하나의 조회 값으로 여러 해당 값을 조회 할 수 있습니다. 위의 gif에서 IFERROR 함수를 사용하여 오류를 포착하고 조건부 서식을 약간 시각적으로 만들었습니다. 위의 예에서는 다른 기사에서 설명한 다른 많은 기능과 기술을 사용했습니다.

파일 다운로드 :