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]

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

The formula should also work in your workbook.

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

If needed, you might want to upload your file to say dropbox for inspection.

 

[CheekyDevil]

Thanks Aladin, yep, can assure you I am entering it as an array formula (CTRL + SHIFT then ENTER, all at the same time )

I may have found a workaround though, it seems by simply adding my data pool to the workbook you sent, rather than the code from your book to mine, it's now functioning, so I may simply need to merge that sheet into my workbook and replace the current sheet.

I think this will be the easiest solution to this as I'm sure you're getting tired of this case I know I've been looking at spreadsheets so long today I'm starting to see gridlines everywhere as if I'm in augmented reality haha.

Once again though I really thank you for everything here. Your time and patience have been invaluable to me and I am extremely appreciative of all your help You're an amazing asset to this community

 

[Aladin Akyurek]

@ CheekyDevil

Creative resolving of the issue!...

By the way, you are welcome and thanks for the update.

 

[markmzz]

The formula should also work in your workbook.

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

If needed, you might want to upload your file to say dropbox for inspection.

Hi Aladin!

I tried you last formula with my file and with your file, but it didn't work (if you don't have data in K1).

My Excel is 2010.

What I am doing wrong? Or I need Excel 365?

=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)),"")


[TABLE="class: grid, width: 1023"]
<colgroup><col><col><col span="2"><col><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]AGENT[/TD]
[TD]TL[/TD]
[TD]TYPE[/TD]
[TD]REF #[/TD]
[TD]NOTES[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Niel[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]08/02/2018[/TD]
[TD]Logan[/TD]
[TD]Niel[/TD]
[TD]Coaching[/TD]
[TD="align: right"]2131[/TD]
[TD]data about logan 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]13/02/2018[/TD]
[TD]Paige[/TD]
[TD]Craig[/TD]
[TD]Feedback[/TD]
[TD="align: right"]5215641651[/TD]
[TD]data about paige[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]**DESIRED RESULT**[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]15/02/2018[/TD]
[TD]Vicky[/TD]
[TD]Toddy[/TD]
[TD]Coaching[/TD]
[TD="align: right"]157891[/TD]
[TD]data about vicky[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Agent Name[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]16/02/2018[/TD]
[TD]Jordan[/TD]
[TD]Dylan[/TD]
[TD]Live Escalation[/TD]
[TD="align: right"]15156[/TD]
[TD]data about jordan[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Ref #[/TD]
[TD]Type[/TD]
[TD]Notes[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]16/02/2018[/TD]
[TD]Amy[/TD]
[TD]Niel[/TD]
[TD]Feedback[/TD]
[TD="align: right"]47851[/TD]
[TD]data about amy[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]20/02/2018[/TD]
[TD]Ali[/TD]
[TD]Toddy[/TD]
[TD]Feedback[/TD]
[TD="align: right"]1789465[/TD]
[TD]data about ali[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]21/02/2018[/TD]
[TD]Debi[/TD]
[TD]Niel[/TD]
[TD]Feedback[/TD]
[TD="align: right"]4178451[/TD]
[TD]data about debi[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]27/02/2018[/TD]
[TD]Logan[/TD]
[TD]Niel[/TD]
[TD]Call Review[/TD]
[TD="align: right"]364872334[/TD]
[TD]data about logan 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**********[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]**************[/TD]
[TD]************[/TD]
[TD]******************[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]**[/TD]
[TD]*******************[/TD]
[TD]***********[/TD]
[TD]******************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[Aladin Akyurek]

Thanks Aladin, yep, can assure you I am entering it as an array formula (CTRL + SHIFT then ENTER, all at the same time )

I may have found a workaround though, it seems by simply adding my data pool to the workbook you sent, rather than the code from your book to mine, it's now functioning, so I may simply need to merge that sheet into my workbook and replace the current sheet.

I think this will be the easiest solution to this as I'm sure you're getting tired of this case I know I've been looking at spreadsheets so long today I'm starting to see gridlines everywhere as if I'm in augmented reality haha.

Once again though I really thank you for everything here. Your time and patience have been invaluable to me and I am extremely appreciative of all your help You're an amazing asset to this community

We can't equate a range to ?*...

So, change the formula to

either...

=IFERROR(INDEX($A$2:$F$9,SMALL(IF($B$2:$B$9=IF($K$1="",$B$2:$B$9,$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)),"")

or

=IFERROR(INDEX($A$2:$F$9,SMALL(IF(ISNUMBER(SEARCH(IF($K$1="","?*",$K$1),$B$2:$B$9)),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)),"")

 

[markmzz]

Re: Returning multiple values from multiple criteriaue

Hi!

Try this small modification in Aladin's formula:

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

Markmzz

Thanks Aladin, yes both you and markmmz

That has worked perfectly! Downloading your workbook has allowed me to see the code working, and then updating it to Marks amendment has done exactly what I wanted it to do

Once again I really cannot thank you enough for all your assistance. I'm expected to have this report functional by the end of the week and could not have met this without your help. You guys are simply amazing and thank you for being so patient with me!

Hi Aladin!

I tried you last formula with my file and with your file, but it didn't work (if you don't have data in K1).

My Excel is 2010.

What I am doing wrong? Or I need Excel 365?

=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)),"")


[TABLE="class: grid, width: 1023"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]AGENT[/TD]
[TD]TL[/TD]
[TD]TYPE[/TD]
[TD]REF #[/TD]
[TD]NOTES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Niel[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]08/02/2018[/TD]
[TD]Logan[/TD]
[TD]Niel[/TD]
[TD]Coaching[/TD]
[TD="align: right"]2131[/TD]
[TD]data about logan 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]13/02/2018[/TD]
[TD]Paige[/TD]
[TD]Craig[/TD]
[TD]Feedback[/TD]
[TD="align: right"]5215641651[/TD]
[TD]data about paige[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]**DESIRED RESULT**[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]15/02/2018[/TD]
[TD]Vicky[/TD]
[TD]Toddy[/TD]
[TD]Coaching[/TD]
[TD="align: right"]157891[/TD]
[TD]data about vicky[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Agent Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]16/02/2018[/TD]
[TD]Jordan[/TD]
[TD]Dylan[/TD]
[TD]Live Escalation[/TD]
[TD="align: right"]15156[/TD]
[TD]data about jordan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ref #[/TD]
[TD]Type[/TD]
[TD]Notes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]16/02/2018[/TD]
[TD]Amy[/TD]
[TD]Niel[/TD]
[TD]Feedback[/TD]
[TD="align: right"]47851[/TD]
[TD]data about amy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]20/02/2018[/TD]
[TD]Ali[/TD]
[TD]Toddy[/TD]
[TD]Feedback[/TD]
[TD="align: right"]1789465[/TD]
[TD]data about ali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]21/02/2018[/TD]
[TD]Debi[/TD]
[TD]Niel[/TD]
[TD]Feedback[/TD]
[TD="align: right"]4178451[/TD]
[TD]data about debi[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]27/02/2018[/TD]
[TD]Logan[/TD]
[TD]Niel[/TD]
[TD]Call Review[/TD]
[TD="align: right"]364872334[/TD]
[TD]data about logan 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**********[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]**************[/TD]
[TD]************[/TD]
[TD]******************[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]**[/TD]
[TD]*******************[/TD]
[TD]***********[/TD]
[TD]******************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

We can't equate a range to ?*...

So, change the formula to

either...

=IFERROR(INDEX($A$2:$F$9,SMALL(IF($B$2:$B$9=IF($K$1="",$B$2:$B$9,$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)),"")

or

=IFERROR(INDEX($A$2:$F$9,SMALL(IF(ISNUMBER(SEARCH(IF($K$1="","?*",$K$1),$B$2:$B$9)),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)),"")

"Que neblina hein!"

Thank you. Now I understand.

Markmzz