Array formula to return multiple people that meet multiple criteria

[Schubby10]

Hi I really need some help with this. Sorry I can’t post the excel file here as it has private information.

What I am trying to do is get all of the names of people that spend over 1,000/ month that work for a specific company.

The names are in column a that need to be returned. The amount is column T and the company is column aq. All other info I do not need for this array query. Does anyone know how to write a formula for this ?

Thank you so much,

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down rows as needed.

Excel Workbook
	A	B	S	T	U	AP	AQ
1	Name			Amount			Company
2	Emp1			200			Com1
3	Emp2			1100			Com2
4	Emp3			565			Com3
5	Emp4			3000			Com4
6	Emp5			250			Com5
7	Emp6			2500			Com2
8	Emp7			125			Com7
9	Emp8			98			Com8
10
11
12	Find	Com2
13	Names	Emp2
14		Emp6
15
Sheet

 

[Schubby10]

Hmm doesn’t seem to produce anything

 

[AhoyNC]

Did you enter the formula with CTRL-SHIFT-ENTER? Excel will put {} around the formula if done right.
Is the example I gave close to what your data looks like.
Also you will need to change ranges to match your actual data.

 

[Schubby10]

I changed the range and that is exactly how my data looks it returns some of them but not all. Any idea why this would be. Thank you for all your help truly and blessing

 

[AhoyNC]

How about posting the formula you are using.
Also, remove the IFERROR part of the formula to see what type of error you get.

INDEX($A$2:$A$9,SMALL(IF($AQ$2:$AQ$9=$B$12,IF($T$2:$T$9>1000,ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$13:B13)))

 

[Schubby10]

=index(data!$a$2:data!$a$20000,small(if(data!$aq$2:$aq$20000=“advertising”,if(data!$t$2:data!$t$20000>1000,row(data!$a2:data!$a$20000)-row(data!$a$2)+1)),rows($a$24:a24))))

Error #num

 

[AhoyNC]

Not sure it works for me. See below using your formula.
Things to check:
Is advertising spelled the same way and no spaces before or after the word.
Are all the values in column T numeric?

Excel Workbook
	A
1	Emp2
2	Emp4
3	Emp5
4
Sheet2

Excel Workbook
	A	B	C	S	T	U	V	AP	AQ
1	Name				Amount				Company
2	Emp1				200				Com1
3	Emp2				1100				advertising
4	Emp3				565				Com3
5	Emp4				3000				advertising
6	Emp5				2500				advertising
7	Emp6				2500				Com2
8	Emp7				125				advertising
9	Emp8				98				Com8
data

 

[Schubby10]

It looks like it is only giving me back odd numbers ( I have a count if formula to give me different names for same co) why would this be?

 

[AhoyNC]

I didn't notice this before but in your part of the formula for the ROW you have data!$a2, it should be data!$a$2.

=index(data!$a$2:data!$a$20000,small(if(data!$aq$2:$aq$20000=“advertising”,if(data!$t$2:data!$t$20000>1000,row(data!$a$2:data!$a$20000)-row(data!$a$2)+1)),rows($a$24:a24))))