목록과 비교하여 셀에서 처음 일치하는 값을 가져오기

키워드 목록이 있을 때， 특정 셀에 다른 여러 값과 함께 포함된 키워드 중 처음 나타난 키워드를 가져오려면INDEX및MATCH수식을AGGREGATE와SEARCH함수와 함께 사용해야 합니다。

목록과 비교하여 셀에서 처음 일치하는 값을 가져오는 방법은？

위 표와 같이 셀에서키워드 목록과 비교하여 처음 일치하는 키워드를 가져오려면정확한 일치가 아닌 포함 일치를 수행해야 합니다。 이를 위해 SEARCH 함수를 사용하여 셀에 나타난 키워드의 위치를 숫자로 추출한 후 AGGREGATE 함수에 전달합니다。 그런 다음 AGGREGATE 함수의function_num 을 15 로, ref2 인수를 1 로 설정하여 가장 작은 숫자를 얻습니다. 이후 MATCH 를 사용하여 가장 작은 값의 위치를 찾아 INDEX 에 전달하여 해당 위치의 값을 가져옵니다。

일반 구문

=INDEX()keyword_rng,MATCH(AGGREGATE(15,6,SEARCH()))keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ 참고： 이는 배열 수식으로， 입력 시Ctrl+Shift+Enter를 눌러야 합니다。

B5 셀에 나타난 키워드 중키워드 열과 비교하여 처음 일치하는 키워드를 가져오려면아래 수식을 C5 셀에 복사하거나 입력한 후Ctrl+Shift+Enter를 눌러 결과를 얻으세요。

=INDEX()$E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH()))$E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

√ 참고： 위의 달러 기호($)는 절대 참조를 나타내며， 수식을 다른 셀로 이동하거나 복사할 때keyword_rng이 변경되지 않음을 의미합니다。 반면lookup_cell에는 달러 기호가 추가되지 않았으므로 동적으로 변경되도록 설계되었습니다。 수식을 입력한 후 채우기 핸들을 아래로 드래그하여 아래 셀에도 수식을 적용하세요。

수식 설명

=INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5):SEARCH 함수는 범위$E$5:$E$7에서 각 키워드의 위치를 숫자로 반환하며， 찾을 수 없을 경우#VALUE!오류를 반환합니다。 결과는 다음과 같은 배열 형태가 됩니다：{15;11;#VALUE!}.
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#VALUE!},1):AGGREGATE 함수는15 의 function_num과(와)6 의 option을 사용하여 오류 값을 무시하면서 배열 내 가장 작은 값을ref2 인수 1에 따라 반환합니다。 따라서 이 코드 조각은11을 반환합니다。
• MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0)=MATCH()11,{15;11;#VALUE!},0):MATCH 함수는0 의 match_type으로 인해 정확히 일치하는 값을 찾아 배열11내에서 해당 값의 위치를 반환합니다。{15;11;#VALUE!}따라서 이 함수는2을 반환합니다。
• INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0)) = INDEX($E$5:$E$7,2):그러면 INDEX 함수는 범위2번째 값인$E$5:$E$7을 반환합니다。 이 값은bbb입니다。

비고

• 셀에 키워드가 하나도 없으면#NUM!오류가 반환됩니다。
• 이 수식은 대/소문자를 구분하지 않습니다。 대/소문자를 구분하는 검색을 수행하려면 SEARCH 함수를 간단히FIND으로 바꾸면 됩니다。

관련 함수

관련 수식