메뉴
실무 위키›응용 공식›엑셀 띄어쓰기가 달라서 못 찾는 이름 검색 공식

엑셀 띄어쓰기가 달라서 못 찾는 이름 검색 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

붙여 쓴 이름과 띄어 쓴 이름을 맞춥니다.

예제파일 다운로드
엑셀 띄어쓰기가 달라서 못 찾는 이름 검색 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01공백을 지운 이름으로 연락처 찾기 (엑셀 2021 이후)

=INDEX ( 연락처 범위, MATCH ( SUBSTITUTE ( 검색 이름, " ", "" ), SUBSTITUTE ( 참조 이름 범위, " ", "" ), 0 ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
검색 이름필수연락처를 찾을 이름이 입력된 셀입니다. 일반 공백의 유무와 관계없이 비교할 수 있는 값입니다.
참조 이름 범위필수연락처 원본 명단의 이름 범위입니다. 공백을 지운 뒤 같은 이름이 둘 이상이면 위에 있는 항목이 먼저 선택되는 범위입니다.
연락처 범위필수참조 이름 범위와 같은 순서로 연락처가 입력된 범위입니다. 참조 이름 범위와 행 수가 같은 범위입니다.
B2=INDEX($E$2:$E$7,MATCH(SUBSTITUTE(A2," ",""),SUBSTITUTE($D$2:$D$7," ",""),0))
A
B
C
D
E
F
1
검색 이름
연락처
참조 이름
연락처 원본
2
박 민준
010-0000-1003
홍 길동
010-0000-1001
3
홍길동
010-0000-1001
김서 연
010-0000-1002
4
정유진
010-0000-1006
박민 준
010-0000-1003
5
김 서연
010-0000-1002
이 하 은
010-0000-1004
6
최도윤
010-0000-1005
최 도윤
010-0000-1005
7
이 하은
010-0000-1004
정 유진
010-0000-1006
8
양쪽 이름에서 키보드로 입력한 일반 공백을 모두 지운 뒤 정확히 같은 이름을 찾습니다. 공백을 지운 결과가 같은 이름이 둘 이상이면 참조 명단에서 먼저 나온 연락처를 반환합니다.

02배열 수식으로 연락처 찾기 (엑셀 2019 이전)

{=INDEX ( 연락처 범위, MATCH ( SUBSTITUTE ( 검색 이름, " ", "" ), SUBSTITUTE ( 참조 이름 범위, " ", "" ), 0 ) )}
인수 설명 자세히 보기
인수구분설명
검색 이름필수연락처를 찾을 이름이 입력된 셀입니다. 일반 공백의 유무와 관계없이 비교할 수 있는 값입니다.
참조 이름 범위필수연락처 원본 명단의 이름 범위입니다. 공백을 지운 뒤 같은 이름이 둘 이상이면 위에 있는 항목이 먼저 선택되는 범위입니다.
연락처 범위필수참조 이름 범위와 같은 순서로 연락처가 입력된 범위입니다. 참조 이름 범위와 행 수가 같은 범위입니다.
B2{=INDEX($E$2:$E$7,MATCH(SUBSTITUTE(A2," ",""),SUBSTITUTE($D$2:$D$7," ",""),0))}
A
B
C
D
E
F
1
검색 이름
연락처
참조 이름
연락처 원본
2
박 민준
010-0000-1003
홍 길동
010-0000-1001
3
홍길동
010-0000-1001
김서 연
010-0000-1002
4
정유진
010-0000-1006
박민 준
010-0000-1003
5
김 서연
010-0000-1002
이 하 은
010-0000-1004
6
최도윤
010-0000-1005
최 도윤
010-0000-1005
7
이 하은
010-0000-1004
정 유진
010-0000-1006
8
엑셀 2019 이전 버전에서는 수식을 입력한 뒤 Ctrl+Shift+Enter를 눌러 배열 수식으로 확정합니다. 중괄호는 엑셀이 자동으로 붙이므로 직접 입력하지 않습니다.
이 공식이 사용하는 함수
INDEX 함수 MATCH 함수 SUBSTITUTE 함수

동작 원리

01공백을 지운 이름으로 연락처 찾기 (엑셀 2021 이후)

SUBSTITUTE 함수가 양쪽 이름에서 일반 공백을 지우고, MATCH 함수가 같은 이름의 위치를 찾으면 INDEX 함수가 그 위치의 연락처를 가져옵니다.

예제 시트에서 박민준의 연락처를 찾는 B2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

검색할 이름에서 공백을 지웁니다

검색 이름 '박 민준'에서 일반 공백을 지우면 비교에 사용할 이름은 '박민준'입니다.

=SUBSTITUTE ( A2, " ", "" )
= 박민준
2

참조 명단에서 같은 이름의 위치를 찾습니다

참조 이름 범위에서도 일반 공백을 지우면 '박민준'은 세 번째에 있습니다.

=MATCH ( "박민준", SUBSTITUTE ( $D$2:$D$7, " ", "" ), 0 )
= 3
3

같은 위치의 연락처를 가져옵니다

연락처 범위의 세 번째 값은 '010-0000-1003'이므로 박민준의 연락처로 반환합니다.

=INDEX ( $E$2:$E$7, 3 )
= 010-0000-1003

02배열 수식으로 연락처 찾기 (엑셀 2019 이전)

SUBSTITUTE 함수가 양쪽 이름에서 일반 공백을 지우고, MATCH 함수가 같은 이름의 위치를 찾으면 INDEX 함수가 그 위치의 연락처를 가져옵니다.

예제 시트에서 박민준의 연락처를 찾는 B2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

검색할 이름에서 공백을 지웁니다

검색 이름 '박 민준'에서 일반 공백을 지우면 비교에 사용할 이름은 '박민준'입니다.

=SUBSTITUTE ( A2, " ", "" )
= 박민준
2

참조 명단에서 같은 이름의 위치를 찾습니다

참조 이름 범위에서도 일반 공백을 지우면 '박민준'은 세 번째에 있습니다.

=MATCH ( "박민준", SUBSTITUTE ( $D$2:$D$7, " ", "" ), 0 )
= 3
3

같은 위치의 연락처를 가져옵니다

연락처 범위의 세 번째 값은 '010-0000-1003'이므로 박민준의 연락처로 반환합니다.

=INDEX ( $E$2:$E$7, 3 )
= 010-0000-1003
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료