문장에서 일치하는 단어 찾기 (2)

나. 두 번째 방법

 

(1) Lookup 함수

VLookup 함수는 많이 다뤄봤는데, Lookup 함수는 낯섭니다. 그렇지만 일련의 범위에서 일치하는 값을 찾은 후 반환값을 찾는 것은 동일합니다.

 

Lookup 함수는 벡터형과 배열형으로 나뉘는데 배열형의 경우는 VLookup 또는 HLookup함수를 사용하라고 권장하고 있습니다.

 

벡터형의 구문은 LOOKUP(lookup_value, lookup_vector, [result_vector])로서

lookup_value는 찾을 값,

lookup_vector는 찾을 대상으로 행이나 열을 한 개만 포함하는 범위,

result_vector는 반환 값이 있는 행이나 열을 한 개만 포함하는 범위입니다.

 

아래 예제의 수식은 =LOOKUP(4.19,G2:G6,H2:H6)로서

4.19를 G2셀부터 G6셀까지의 범위에서 찾은 후 일치하는 행의 값을 H2셀에서 H6셀까지의 범위에서 찾는 것입니다.

 

VLookup 함수보다는 XLookup 함수와 비슷합니다.

XLookup함수의 구문은

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 로서

첫 번째 인수가 lookup_value(찾을 값),

두 번째 인수가 lookup_array(찾을 대상 범위),

세 번째 인수가 return_array(반환 값 범위)이기 때문입니다.

 

(2) Lookup 함수를 이용한 수식 (실패)

위와 같이 전체가 일치하면 위처럼 Lookup함수를 사용할 수 있는데

문제는 일부만 일치한다는 것입니다.

 

다운로드

 

 

따라서, Match 함수를 이용한 수식처럼

lookup_vector를 범위가 아니라 수식으로 지정하고,

lookup_value를 true 또는 1로 지정해야 합니다.

 

이렇게 하면 수식은

=LOOKUP(TRUE,ISNUMBER(FIND($D$2:$D$4,E6)),$D$2:$D$4)가 되는데,

IsNumber 수식의 값이 True 또는 False이기 때문에 찾을 값을 True로 해야지,

 

1로 해서 =LOOKUP(1,ISNUMBER(FIND($D$2:$D$4,E6)),$D$2:$D$4)라고 하면 일치하는 값이 있는데도 #N/A라고 에러 값이 나오므로 lookup_vector의 값과 lookup_value가 일치하도록 해야 합니다.

 

1을 True로 돌려놓고, D11셀의 오른쪽 아래 네모 모양의 채우기 핸들을 D14셀까지  끌어서 수식을 복사하면

두 번째와 세 번째 값이 사과여야 하는데 수박이라고 나옵니다.

 

(3) Lookup 함수를 이용한 수식 (성공)

수식을 아래와 같이 수정해야 합니다.

=LOOKUP(0,0/ISNUMBER(FIND($D$2:$D$4,E6)),$D$2:$D$4)

 

찾을 값을 0으로 하고,

lookup_vector를 기존 수식 앞에 0/를 붙여서, ISNUMBER(FIND($D$2:$D$4,E6))가 False일 때는 0/0이 돼서 에러가 발생하도록 하고, ISNUMBER(FIND($D$2:$D$4,E6))가 True일 때는 0/1이니 0을 반환하도록 해야 합니다.

 

0/ISNUMBER(FIND($D$2:$D$4,E6))를 마우스로 끌어서 범위로 잡고 커서를 올려놓으면 Microsoft 365의 경우는 수식 줄 위에 결괏값 #DIV/0!; #DIV/0!;0이 표시되며,

 

이전 버전이라면 위와 같이 범위가 선택된 상태에서 F9키를 누르면

수식의 결괏값인 #DIV/0!; #DIV/0!;0으로 바뀌므로 수식의 결괏값을 알 수 있습니다.

 

다시 원래의 수식으로 돌아가려면 Esc키를 누릅니다.

 

D11셀의 수식을 채우기 핸들을 더블 클릭해서 복사하면 D11부터 D14셀까지도 모두 정확한 값이 구해졌습니다.

 

Lookup 함수의 특이한 점은  찾을 값에 0이 아니라 다른 값을 써도 된다는 것입니다.

 

특정 문자 찾기(해법)2.xlsx

0.01MB