=IF(ROWS($1:2)+1-MATCH(9.9E+307,$C$1:C2)<=COUNTIF($A$1:$A$7000,LOOKUP(9.9E+307,$C$1:C2)),INDEX($B$1:$B$7000,SMALL(IF($A$1:$A$7000=LOOKUP(9.9E+307,$C$1:C2),ROW($B$1:$B$7000)),ROWS($1:2)+1-MATCH(9.9E+307,$C$1:C2))),"")
Sample1.xls | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | |||||||
2 | 5/4/2005 | Dog | 5/5/2005 | cat | |||
3 | 5/5/2005 | cat | Moues | ||||
4 | 5/5/2005 | Moues | Cow | ||||
5 | 5/4/2005 | Pig | |||||
6 | 5/5/2005 | Cow | |||||
7 | 5/4/2005 | Dog | |||||
8 | Pig | ||||||
9 | |||||||
10 | |||||||
11 | |||||||
12 | |||||||
Sheet1 |
Sample1.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ||||||
2 | 02-01349 | Dog | 02-01-349 | #N/A | ||
3 | 407657-0004 | Cat | #N/A | |||
4 | 02-01350 | Mouse | #N/A | |||
5 | 407657-0005 | Pig | #N/A | |||
6 | 02-01351 | Cow | #N/A | |||
7 | 407657-0005 | #N/A | ||||
8 | #N/A | |||||
9 | #N/A | |||||
10 | ||||||
11 | ||||||
Sheet3 |
I notice it does not work if there are dashes ( - ) in the part#.