Returning multiple values from multiple criteria

[CheekyDevil]

Hey all,

I'm in desperate need for a code that will allow the return of multiple values from a line of cells (of which I can then CONCATENATE) using a given criteria.

For context, my headings are:

A1 = Date B1 = Agent C1 = TL D1 = Type E1 = Ref# F1 = Comments

What I need is a code that allows me to enter a TL name in a cell (say L1 to keep it out of the way), and return all the agents and corresponding data from A to F that have this TL in the data range (A1:F5000)

Then (if possible) if I can further narrow it down to return the result of all items from a particular agent (name entered in K1), for example, all data from Dave (agent) in Allen's team (TL).

I've tried using an INDEX + MATCH combination but as it turns out I'm not at this level to understand this formula just yet HAHA

Thank you for any help you can provide

 

[Aladin Akyurek]

are you saying that k1 can be empty, not l1?

 

[CheekyDevil]

That's right L1 will always have the TL's name there (as it auto-populates as soon as they open the userform) and they can then drill down to an individual agent if desired or view all their agents feedback.

Now for some reason I cannot fathom, your workbook works perfectly (again though with marks slight modification) but when I try to copy the code to my main sheet it again gives blank results. Once again though I am certain this is an ID 10 T error code, and PEBKAC hahaha

 

[Aladin Akyurek]

That's right L1 will always have the TL's name there (as it auto-populates as soon as they open the userform) and they can then drill down to an individual agent if desired or view all their agents feedback.

Now for some reason I cannot fathom, your workbook works perfectly (again though with marks slight modification) but when I try to copy the code to my main sheet it again gives blank results. Once again though I am certain this is an ID 10 T error code, and PEBKAC hahaha

Then the formula I suggested becomes:

=IFERROR(INDEX($A$2:$F$9,SMALL(IF($B$2:$B$9=IF($K$1="","?*",$K$1),IF($C$2:$C$9=$L$1,ROW($A$2:$F$9)-ROW($A$2)+1)),ROWS($K$6:K6)),MATCH(K$5,$A$1:$F$1,0)),"")

My usage of ?* has a reason: Both B and C are text, hence this usage makes the formula a tad less inefficient.

When you edit the formula (or copy-paste), you need to re-confirm the formula with control+shift+enter.

 

[CheekyDevil]

Hmm I really cannot explain what is happening here. I copy your formula precisely (no spaces or any other characters), paste it into the cell K6 and immediately hit C+S+E (pressing nothing else), it shows as an array formula { } at each end, yet still shows no data, as if it's giving the IFERROR(,"") as the result. Even after dragging across to M6, and down.

What I can't explain is why this code is working perfectly on your workbook, and the sheet I am copying it into is an exact replica of yours (albeit with full names as the reference but this should make no difference) but all the same references and cell locations.

 

[Aladin Akyurek]

Hmm I really cannot explain what is happening here. I copy your formula precisely (no spaces or any other characters), paste it into the cell K6 and immediately hit C+S+E (pressing nothing else), it shows as an array formula { } at each end, yet still shows no data, as if it's giving the IFERROR(,"") as the result. Even after dragging across to M6, and down.

What I can't explain is why this code is working perfectly on your workbook, and the sheet I am copying it into is an exact replica of yours (albeit with full names as the reference but this should make no difference) but all the same references and cell locations.

If you adjusted the formula in some way, please post the adjusted formula.

Also, What do you get when you try the following?

=COUNTIFS($B$2:$B$9,K1)

=COUNTIFS($C$2$$C$9,L1)

 

[CheekyDevil]

Thanks, the COUNTIFS only returned a 1 result (so indicates a positive but not the cell value)

Also, the only amendment to the code was expanding the cells it references:

Original Code:

=IFERROR(INDEX($A$2:$F$9,SMALL(IF($B$2:$B$9=IF($K$1="","?*",$K$1),IF($C$2:$C$9=$L$1,ROW($A$2:$F$9)-ROW($A$2)+1)),ROWS($K$6:K6)),MATCH(K$5,$A$1:$F$1,0)),"")

Amended:
=IFERROR(INDEX($A$2:$F$90,SMALL(IF($B$2:$B$90=IF($K$1="","?*",$K$1),IF($C$2:$C$90=$L$1,ROW($A$2:$F$90)-ROW($A$2)+1)),ROWS($K$6:K6)),MATCH(K$5,$A$1:$F$1,0)),"")

 

[Aladin Akyurek]

Thanks, the COUNTIFS only returned a 1 result (so indicates a positive but not the cell value)

1 for what? For K1 or L1?

I'm asking for both...

=COUNTIFS($B$2:$B$90,K1)

=COUNTIFS($C$2$$C$90,L1)

Additionally, what do we get with also the following?

=COUNTIFS($B$2:$B$90,"?*")

=COUNTIFS($C$2$$C$90,"?*")

Also, the only amendment to the code was expanding the cells it references:

[...]

=IFERROR(INDEX($A$2:$F$90,SMALL(IF($B$2:$B$90=IF($K$1="","?*",$K$1),IF($C$2:$C$90=$L$1,ROW($A$2:$F$90)-ROW($A$2)+1)),ROWS($K$6:K6)),MATCH(K$5,$A$1:$F$1,0)),"")

That looks ok.

 

[CheekyDevil]

Sorry, using your workbook,

=COUNTIFS($B$2:$B$9,K1) returns 2, (Entered in K6)
=COUNTIFS($C$2$$C$9,L1) returns 4. (Entered in L6)

=COUNTIFS($B$2:$B$90,"?*") returns 8 (Entered in K6)
=COUNTIFS($C$2:$C$90,"?*") returns 8 (Entered in L6)

 

[Aladin Akyurek]

Sorry, using your workbook,

=COUNTIFS($B$2:$B$9,K1) returns 2, (Entered in K6)
=COUNTIFS($C$2$$C$9,L1) returns 4. (Entered in L6)

=COUNTIFS($B$2:$B$90,"?*") returns 8 (Entered in K6)
=COUNTIFS($C$2:$C$90,"?*") returns 8 (Entered in L6)

Please answer these questions using your workbook.

=COUNTIFS($B$2:$B$90,K1)

=COUNTIFS($C$2$$C$90,L1)

=TRIM(K1)

=TRIM(L1)

What do these give?

 

[CheekyDevil]

No worries, in my workbook, using Logan as the agent and Niel as the TL, these return:

=COUNTIFS($B$2:$B$90,K1) 2

=COUNTIFS($C$2$$C$90,L1) 5

=TRIM(K1) Logan

=TRIM(L1) Niel