중복 VLOOKUP - 2

아래와 같이 B2에 문자열을 입력하면 E열에서 비슷한 문자열을 모두 찾아오고 싶을 때, 두 가지 방식이 있어 보입니다. 각각 장단점은 있겠습니다.

 

방식1은 데이타 원본에 보조열을 사용하여 COUNTIF로 누계를 매기고 그 숫자를 활용하여 VLOOKUP으로 가져오는데, 실전적이며 간결합니다. (보조열 없이 함수로 가능할지 의문, 된다 해도 무쟈게 복잡해질 것임.)

 

방식2는 데이타 원본을 건드리지 않고 싶을 경우 행번호를 배열수식으로 가져오는데, 약간 복잡하고 데이타 수가 많으면 속도가 느려지는 단점이 있습니다. (해결하려면 VBA를 돌리면 되겠지요.)

 

	A	B	C	D	E
1		(방식1)
2	3	2268525		1	2268525DSA79*
3	1	2268525DSA79*		1	1172329WEP55*
4	2	2268525DAP45*		2	2268525DAP45*
5	3	2268525DAP45*		2	1172329DAP69*
6	4	#N/A		3	2268525DAP45*
7
8		(방식2)			2268525DSA79*
9	8	2268525DSA79*			1172329WEP55*
10	10	2268525DAP45*			2268525DAP45*
11	12	2268525DAP45*			1172329DAP69*
12	#NUM!	#NUM!			2268525DAP45*

 

A2 =SUMPRODUCT(N(ISNUMBER(FIND(B2,E2:E6,1)))) ; 참고용, B2 문자열이 들어있는 E2:E6의 데이타 수
D2 =COUNTIF(E$2:E2,"*"&B$2&"*")                            ; 해당문자열이 들어있는 데이타 개수의 누계
A3 =ROW(A1)                                                           ; 일련번호 매기기
B3 =VLOOKUP(A3,D$2:E$6,2,0)                                  ; 일련번호에 해당하는 데이타 가져오기
A9 =SMALL(IF((COUNTIF(A$8:A8,ROW(E$8:E$12))=0)*(ISNUMBER(FIND(B$2,E$8:E$12,1))),ROW(E$8:E$12)),1)                                                         ; 대상 데이타 행 번호 중 처음 나온 행 번호
B9 =INDIRECT("E"&A9)                                             ; 해당 행번호의 데이타 가져오기

 

A9는 배열수식입니다. 수식 입력 후 CTRL+SHIFT+ENTER 하세요. 색상이 있는 셀을 잡아 아래로 드래그 복사하면 표가 완성됩니다.