Returning multiple values from multiple criteria

Try to post a small sample that Excel can read along with the expected result(s).

Sure thing, I tried to do this using a table on the initial post but I failed.... miserably

I really hope this helps (I've used an excel sheet but not sure on the pasted format)



So from A1 to F5000 is going to be data entries from a user form (simply mentioned as this is going to be my 'data pool'). What I am developing is a tool for the agents Team Leaders to pull the data from this pool to narrow down the results to only their agents, and then further still to an individual agent.

In summary, if only the TL name is populated, show all data within the range A1 to F5000 (or greater) with all the agents in their team, and if an individual name is selected, narrow these down to only the data from this agent.

In all entries, the TL of the agent is going to be in the adjacent cell next to the agent name (I,e Agent is in A1, their TL will be in B1)

Again I really hope this clarifies my situation as I'm not the best at explaining myself

Thank you for your time and potential assistance

Thanks for the sample and expected results...

Row\Col	A​	B​	C​	D​	E​	F​	K​	L​	M​
1​	DATE	AGENT	TL	TYPE	REF	NOTES	Logan	Niel
2​	8/2/2018​	Logan	Niel	Coaching	2131​	data about logan 1
3​	13/02/2018​	Paige	Craig	Feedback	5215641651​	data about paige	**DESIRED RESULT**
4​	15/02/2018​	Vicky	Toddy	Coaching	157891​	data about vicky	Agent Name
5​	16/02/2018​	Jordan	Dylan	Live Escalation	15156​	data about jordan	Ref	Type	Notes
6​	16/02/2018​	Amy	Niel	Feedback	47851​	data about amy	2131​	Coaching	data about logan 1
7​	20/02/2018​	Ali	Toddy	Feedback	1789465​	data about ali	364872334​	Call Review	data about logan 2
8​	21/02/2018​	Debi	Niel	Feedback	4178451​	data about debi
9​	27/02/2018​	Logan	Niel	Call Review	364872334​	data about logan 2

In K6 control+shift+enter, not just enter, copy across, and down:

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

Thank you very much for the help there Aladin. I sincerely appreciate it, however I regret to advise it has not yet performed as intended. I can play around with the code and see if I can get it to work as I think it may need to have the references expanded (from F9 to F-ongoing lol) but I think I can work with it as it is

Again I really thank you for taking time to do that for me It's been a real head-scratcher for a while now

CheekyDevil said:

Thank you very much for the help there Aladin. I sincerely appreciate it, however I regret to advise it has not yet performed as intended. I can play around with the code and see if I can get it to work as I think it may need to have the references expanded (from F9 to F-ongoing lol) but I think I can work with it as it is

Again I really thank you for taking time to do that for me It's been a real head-scratcher for a while now

Click to expand...

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

CheekyDevil said:

Thank you very much for the help there Aladin. I sincerely appreciate it, however I regret to advise it has not yet performed as intended. I can play around with the code and see if I can get it to work as I think it may need to have the references expanded (from F9 to F-ongoing lol) but I think I can work with it as it is

Again I really thank you for taking time to do that for me It's been a real head-scratcher for a while now

Click to expand...

What did I miss if I'm understanding your comment correctly?

Hey thank you both very much for helping here!

Not too sure why, but I copy and paste the code to K6 (ensuring I CTRL + SHIFT + ENTER also to make it an array formula) and dragged it down, however it returns blank results, even when I copy and paste the names in the references to ensure accuracy.

I do have a sample sheet I can send but cannot work out how to upload it to this thread... (I'm awaiting the "NEWBIE" remarks hehe)

I suppose the easiest way of explaining this is I need it to "auto filter" the invalid results from A2 to F~ (as this will be an ever expanding data set) and only show the corresponding agents data in the row.

CheekyDevil said:

Hey thank you both very much for helping here!

Not too sure why, but I copy and paste the code to K6 (ensuring I CTRL + SHIFT + ENTER also to make it an array formula) and dragged it down, however it returns blank results, even when I copy and paste the names in the references to ensure accuracy.

I do have a sample sheet I can send but cannot work out how to upload it to this thread... (I'm awaiting the "NEWBIE" remarks hehe)

I suppose the easiest way of explaining this is I need it to "auto filter" the invalid results from A2 to F~ (as this will be an ever expanding data set) and only show the corresponding agents data in the row.

Click to expand...

Both?

Here is the workbook which implements the set up I described: https://www.dropbox.com/s/mf3owhflrpg171j/CheekyDevil%20Returning%20multiple%20values%20from%20multiple%20criteria.xlsx?dl=0

Hope this helps.

P.S. Note that the set up expects an agent in K1 while L1 can be empty if so desired.

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!