CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulagoogle-sheets-formulaarray-formulas
Captionless
Captionless

Reputation: 63

Multiple Values using single Criteria

enter image description hereI need help with my office work. I am trying to look for a formula where I have one database that contains the list of all supervisors and all their analyst (15 per team).

I have tried 

=IFERROR(INDEX(Database!$A$1:$A$350,SMALL(IF($F$7=Database!$I$1:$I$350,ROW(Database!$A$1:$A$350)-1,""),ROW()-1)),"")

What I am trying to do is I want to do a lookup using the supervisors list and employee id list and it will show all their emp id on the yellow part.

I have updated a screenshot of the idea,

Upvotes: 1

Views: 307

Answers (3)

player0
player0

Reputation: 1

paste in C2 cell:

=FILTER(F:F, G:G=C1)

paste in C17 cell:

=FILTER(F:F, G:G=C16)

0

also you could just use:

=FILTER(F:F, G:G=INDIRECT("C"&ROW()-1))

0

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8375

So, have a look at this: (Note, submitted before the source data structure was evident, but left as it may be a helpful solution for others...)

enter image description here

Cell B2 is a drop down from data validation to select the supervisor you want to work with.

Drag the choose down for the number you need.

Upvotes: 2

JvdV
JvdV

Reputation: 75860

If your data is been set up in two columns, you might look into use of SMALL. It's an array solution though:

Google Spreadsheets:

enter image description here

Formula in C2:

=ARRAYFORMULA(INDEX($F$1:$F$27,SMALL(($G$1:$G$27=$C$1)*ROW($G$1:$G$27),COUNTIF($G$1:$G$27,"<>"&C$1)+ROW(A1))))

Excel:

Same formula (almost) but entered as array using CtrlShiftEnter:

=INDEX($F$1:$F$27,SMALL(($G$1:$G$27=$C$1)*ROW($G$1:$G$27),COUNTIF($G$1:$G$27,"<>"&C$1)+ROW(A1)))

You can replicate the formula for C17 but just need to edit the cell reference to C17. Drag the formula down.

Upvotes: 2

Related Questions