Array formula to return multiple people that meet multiple criteria

Schubby10

New Member
Joined
Aug 16, 2018
Messages
33
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,
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
When I copied in your formula I got an error, however when I retyped "advertising" it worked???

I assume want to start entering data on ROW 24 based on your formula. If so, copy this formula into your spreadsheet.
Then press F2 for edit and then CTRL-SHIFT-ENTER to put in the formula. Then drag (not copy) the formula down the row.

Code:
[TABLE="width: 1250"]
<colgroup><col width="1250"></colgroup><tbody>[TR]
   [TD="width: 1250"]=IFERROR(INDEX(data!$A$2:$A$20000,SMALL(IF(data!$AQ$2:$AQ$20000="advertising",IF(data!$T$2:$T$20000>1000,ROW(data!$A$2:$A$20000)-ROW(data!$A$2)+1)),ROWS($A$24:A24))),"")[/TD]
 [/TR]
</tbody>[/TABLE]
 
Upvote 0
Does this diagnostic change help?

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(data!$A$2:$A$20000,SMALL(IF(TRIM(data!$AQ$2:$AQ$20000)="advertising",IF(ISNUMBER(ata!$T$2:$T$20000),IF(data!$T$2:$T$20000>1000,ROW(data!$A$2:$A$20000)-ROW(data!$A$2)+1))),ROWS($1:1))),"")
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top