Return Multiple Matches

[Nanogirl21]

I hope everyone is going well and staying safe.

How can I use a formula to return all results that match the criteria? Some results will have multiple returns. I’d like those to show all results in 1 cell separated by a Char(10) returns.

Search criteria are in cell A2 and AC2 on Sheet 1 (The results must match both).
Data is on Sheet 2 Columns A-C. The formula would search Col. A and B returning Col. C
Results would be on Sheet 1 cell AC3

Example:
Sheet 1 Criteria A2: Manager
Sheet 1 Criteria AC2: 132A

Sheet 2 Data

COL A:	COL B:	COL: C
Manager	132A	John
Supervisor	132C	Mike
Team	132C	Johnny
Manager	132B	Tom
Manager	132A	Chris

Expected Results Sheet 1 Cell AC3

John Chris

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Nanogirl21]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have Microsoft/Excel 2013.

 

[Fluff]

Thanks for updating your profile.
With 2013 you will be better of with a UDF rather than a formula, is that ok?

 

[Nanogirl21]

Thanks for updating your profile.
With 2013 you will be better of with a UDF rather than a formula, is that ok?

Thank you. I plan to incorporate this into a larger sheet that I am using for reporting. It would be easier on my end with a formula. Also, multiple users and skill levels will be using this, so I'm not sure how that would work.

 

[Fluff]

With 365 or 2019, it would be simple to do it with a formula, but for 2013 it would be pretty horrendous, especially if you have a lot of data.
A UDF is used like a formula, but there is a macro in the background, so macros will need to be enabled for all users & it would only work in a desktop version.

 

[Nanogirl21]

With 365 or 2019, it would be simple to do it with a formula, but for 2013 it would be pretty horrendous, especially if you have a lot of data.
A UDF is used like a formula, but there is a macro in the background, so macros will need to be enabled for all users & it would only work in a desktop version.

Okay. Thank you for explaining. Can you please help with the UDF version.

 

[Fluff]

Ok, put this in a standard module

Function Nanogirl(Level As String, Dept As String, Data As Range) As String
   Dim Cl As Range
   For Each Cl In Data.Columns(1).Rows
      If Cl.Value = Level And Cl.Offset(, 1) = Dept Then
         Nanogirl = Nanogirl & vbLf & Cl.Offset(, 2).Value
      End If
   Next Cl
   Nanogirl = Mid(Nanogirl, 2)
End Function

And it's used like

+Fluff 1.xlsm
	A																											AB	AC
1
2	Manager																												132A
3																													John Chris
Sheet1

Cell Formulas
Range	Formula
AC3	AC3	=Nanogirl(A2,AC2,Sheet2!A2:C6)

+Fluff 1.xlsm
	A	B	C
1	Level	Dept	Name
2	Manager	132A	John
3	Supervisor	132C	Mike
4	Team	132C	Johnny
5	Manager	132B	Tom
6	Manager	132A	Chris
Sheet2

 

[Nanogirl21]

Ok, put this in a standard module

Function Nanogirl(Level As String, Dept As String, Data As Range) As String
   Dim Cl As Range
   For Each Cl In Data.Columns(1).Rows
      If Cl.Value = Level And Cl.Offset(, 1) = Dept Then
         Nanogirl = Nanogirl & vbLf & Cl.Offset(, 2).Value
      End If
   Next Cl
   Nanogirl = Mid(Nanogirl, 2)
End Function

And it's used like

+Fluff 1.xlsm
	A																											AB	AC
1
2	Manager																												132A
3																													John Chris
Sheet1

Cell Formulas
Range	Formula
AC3	AC3	=Nanogirl(A2,AC2,Sheet2!A2:C6)

+Fluff 1.xlsm
	A	B	C
1	Level	Dept	Name
2	Manager	132A	John
3	Supervisor	132C	Mike
4	Team	132C	Johnny
5	Manager	132B	Tom
6	Manager	132A	Chris
Sheet2

I am not getting any results. I followed your steps, but the AC3 where I entered the formula is blank.

 

[Fluff]

What is the name of the sheet & the range with the data?
Also did you put the code in a standard module in the same workbook with the data?