거래처명에서 일치하는 고객명 찾기(1) - Find 함수

1. 문제

 

아래와 같이 당월 시트의 B열에는 거래처명이 있고, 고객 시트에 고객명이 있는데,

거래처명 중 일치하는 고객명을 추출하는데, 김영희와 영희는 모두 만족하지만 긴 고객명을 출력해야 합니다.

 

 

거래처명에서 일치하는 고객명 찾기.xlsx

0.01MB

 

 

2. 해결 방안

여러 가지 방법이 있을 수 있습니다.

Find 함수를 이용할 수도 있고, 365 버전이라면 Scan 함수를 이용할 수도 있습니다.

각각 해결 방법을 알아보겠습니다.

 

가. Find 함수

 

(1) Find 함수의 구문

 

FIND(find_text, within_text, [start_num])로서

find_text는 찾을 문자열, within_text는 찾는 문자가 들어 있는 문자열 또는 범위, start_num은 찾기 시작할 위치인데, 대괄호 안에 있으므로 옵션입니다.

 

(2) 일치하는 고객명의 위치 찾기

Find는 함수는 일반적으로 하나의 문자열만을 찾는 것이지만,

여러 개의 문자열을 찾을 수도 있는데,

이 때는 찾을 범위를 배열(범위)로 넣으면 됩니다.

 

위와 같은 경우는 고객명을 찾는 것이므로 고객명을 찾을 범위에 넣고, 문자가 들어 있는 문자열을 B2셀을 지정해서

=FIND(고객!$A$2:$A$14,B2)라고 하면 됩니다.

 

그런데 배열 수식이므로 365 버전에서는 D2셀에만 수식을 넣더라도 고객의 범위인 13개의 결과가 출력되는데,

 

종전 버전이라면 D2셀에서 D14셀까지 범위를 먼저 선택한 후 수식을 입력하고 CSE(Ctrl + Shift + Enter)키를 누르면 모든 셀에 결괏값이 표시됩니다.

 

(3) 위치 최솟값 찾기

위 수식의 결과는 고객 중 두 번째인 김영희와 여섯 번째 고객인 영희가 일치하는데 일치하는 위치가 1과 2라는 의미입니다.

다시 말해 김영희는 '김영희 전파사'에서 첫 번째 위치부터 있고,

영희는 '김영희 전파사'에서 두 번째 위치부터 있습니다.

 

그런데 위치가 작은 것을 구하는 것은 아래와 같이 하면 됩니다.

MIN(IFERROR(FIND(고객!$A$2:$A$14,B2),100))

 

(4) 일치하는 고객 중 가장 긴 이름의 행 찾기

단순히 행의 max를 구하면 길이와 무관하게 행이 구해지므로 안되고,

 

시작 위치가 가장 작은(Min) 값을 찾아야 하며 최솟값이므로 에러 일 때 값을 큰 수인 100으로 줬습니다.

MIN(IFERROR(FIND(고객!$A$2:$A$14,B2),100))

 

그것과 시작 위치가 일치하는 것을 찾는데 이 때는 에러일 때 값으로 0을 주면 됩니다.

IFERROR(FIND(고객!$A$2:$A$14,B2),0)

 

이와 같은 조건이 맞을 경우 행 수를 반환하도록 하는데,

ROW(고객!$A$2:$A$14)

 

최댓값을 구해야 합니다.

 

따라서, 전체 수식은

=MAX(IF(MIN(IFERROR(FIND(고객!$A$2:$A$14,B2),100))=IFERROR(FIND(고객!$A$2:$A$14,B2),0),ROW(고객!$A$2:$A$14),0))

가 됩니다.

 

이제 인덱스 함수와 결합해 이름을 찾으면 되는데, 행 수가 3이 반환되므로 index의 첫 번째 인수를 고객!$A$1:$A$14라고 1행부터 지정해야 합니다. 

=INDEX(고객!$A$2:$A$14,MAX(IF(MIN(IFERROR(FIND(고객!$A$2:$A$14,B2),100))=IFERROR(FIND(고객!$A$2:$A$14,B2),0),ROW(고객!$A$2:$A$14),0))-1)

 

만약 2행부터 지정하려면 행수에서 -1을 해야 합니다.

 

(5) 찾는 고객이 없을 때 처리

D2셀의 채우기 핸들을 더블 클릭하면

고객명이 없을 경우 #VALUE! 에러가 발행하므로 

 

ifError 함수를 이용해 에러일 때 값으로 "없음" 또는 ""을 지정하면 됩니다.

 

거래처명에서 일치하는 고객명 찾기(1).xlsx

0.01MB