vlookup with multiple results

[AceFI]

Hi all,
i'm sure this has been done to death... but can't seem to find an answer...

i have a 'prospect database' that has about 2500 results in it, for a variety of employees.. some prostects are 'dead' some not.
Daily, i'm wanting to on a separate sheet, list all of the 'current' prospects, and also on the same sheet return '12 month follow up' prospects

I can do this no problem - as long as there is only 1 result...

any ideas / suggestions on this?
The lead database is numerically sequential

thanks

 

[AceFI]

Because it flagged as 'dead' with a 'Y'

 

[AliGW]

Thank you. Before proceeding, is there any other 'filtering' we need to take into account? I know this is all obvious to you.

 

[AceFI]

its hard when you don't use it every day pretty much the main filter is REP and DEAD
thanks

 

[AliGW]

OK. So, have a look at this. The reason some of the columns are not pulling through is that the headings don't match sheet 1 - you need to make them match for this to work properly. The formula in A4 is an array formula and needs entering with CTRL+SHIFT+ENTER, and then drag copying down. The formula in B4 is a standard formula entered with ENTER and drag copied down and across.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	REP:	A
2
3	Lead #	Date In	First	Last	Email	Phone	P/C	Lead Type	Source		Interested In	Purchased?	Marketing Opt Out	Dead	Contact 1	Contact 2
4	2	17/03/2017	alex	AA	alexAA@gmail.com	0400 222 333		Referal Prospect				no	No	00/01/1900	Manager LMTC 1/5/17	13/4/17 Emailed Finance email, with $100 fuel voucher
5	3	22/03/2017	michaek	Kerlin	michaekKerlin@gmail.com	0400 333 444		Sales Lead				no	y	0.00	Manager LMTC 1/5/17	13/4/17 Emailed Finance email, with $100 fuel voucher
6	19	03/04/2017	Gordon	Brown	GordonBrown@gmail.com	0400 333 444		Sales Lead				no	No	0.00	NO RETURN CONTACT	13/4/17 Emailed Finance email, with $100 fuel voucher
7	20	03/04/2017	Kerry	Gardner	KerryGardner@gmail.com	0400 444 555		Referal Prospect				yes	No	0.00	Manager LMTC 1/5/17	00/01/1900

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B4 =IFNA(INDEX(Sheet1!$B$2:$P$28,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$28,0),MATCH(Sheet2!B$3,Sheet1!$B$1:$P$1,0)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A4 {=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$J$2:$J$28=Sheet2!$B$1,IF(Sheet1!$N$2:$N$28="",ROW($A$2:$A$28))),ROWS($A$1:A1))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>