Lookup multiple criteria

[dangerousdaveeg]

Hello

I have looked online and tried multiple different suggestions but I must be doing something wrong as I cant get the desired result.

What I want to do is.

In cell A60 is a Data validation list derived from all of the names in cells A2:A55
In cell A61 I wanted a formula to look for the name from A60 within the range A2:A55 and then look for any instance of the number 1 within range B2:AE55 and for every match of the number 1 I wanted to return the dates they relate to that is in range B1:AE1

Below is a screenshot of the data that hopefully helps with what I want to achieve, So as an example I would want the end result to show 5/2/2022 & 26/2/2022

Thanks

Dave

 

[KOKOSEK]

Could you update Excel version in your profile, please? It helps to find out a solution.

 

[Toadstool]

Are the Agents in A2 to A55 unique or may one Agent appear multiple times?

 

[dangerousdaveeg]

Are the Agents in A2 to A55 unique or may one Agent appear multiple times?

The agents would be unique

 

[dangerousdaveeg]

Could you update Excel version in your profile, please? It helps to find out a solution.

I have update with the version I am using, Which is 2013 for reference.

 

[dangerousdaveeg]

Mini sheet below

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Name	05/02/2022	12/02/2022	19/02/2022	26/02/2022	05/03/2022	12/03/2022	19/03/2022	26/03/2022	02/04/2022	09/04/2022	16/04/2022	23/04/2022	30/04/2022	07/05/2022	14/05/2022	21/05/2022	28/05/2022	04/06/2022	11/06/2022	18/06/2022	25/06/2022	02/07/2022	09/07/2022	16/07/2022	23/07/2022	30/07/2022	06/08/2022	13/08/2022	20/08/2022	27/08/2022
2	Agent 1	1			1													1					1				1
3	Agent 2																	1					1				1
4	Agent 3																			1				1					1
5	Agent 4																			1				1					1
6	Agent 5													1										1							1
7	Agent 6	1												1										1							1
8	Agent 7	1													1									1						1
9	Agent 8	1													1									1						1
10	Agent 9		1						1						1										1
11	Agent 10		1						1						1										1
12	Agent 11		1					1							1										1
13	Agent 12							1							1								1								1
14	Agent 13			1						1						1									1
15	Agent 14			1						1						1									1
16	Agent 15			1							1												1			1
17	Agent 16				1						1					1										1
18	Agent 17				1							1				1
19	Agent 18				1											1			1							1
20	Agent 19					1										1										1
21	Agent 20						1					1					1									1
22	Agent 21					1											1						1			1
23	Agent 22					1											1				1					1
24	Agent 23						1										1				1						1
25	Agent 24						1										1										1
26	Agent 25						1										1					1					1
27	Agent 26						1											1				1					1
28	Agent 27							1					1										1				1
29	Agent 28							1					1					1										1
30	Agent 29							1										1						1				1
31	Agent 30	1						1										1										1
32	Agent 31							1							1								1					1
33	Agent 32								1					1				1											1
34	Agent 33								1					1					1							1
35	Agent 34								1										1					1				1
36	Agent 35								1										1							1		1
37	Agent 36									1					1				1										1
38	Agent 37									1							1			1					1
39	Agent 38				1					1									1				1
40	Agent 39									1							1			1					1
41	Agent 40										1																		1
42	Agent 41										1					1				1										1
43	Agent 42					1					1					1							1
44	Agent 43						1				1									1										1
45	Agent 44						1					1								1										1
46	Agent 45											1									1								1	1
47	Agent 46					1						1	1								1
48	Agent 47				1							1							1		1
49	Agent 48				1							1							1		1
50	Agent 49			1									1								1									1
51	Agent 50			1									1									1								1
52	Agent 51		1											1								1									1
53	Agent 52		1										1									1									1
54	Agent 53	1												1								1									1
55	Agent 54	1											1									1									1
56
57
58
59
60	Agent 1
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A60	List	=$A$2:$A$55

 

[Toadstool]

Does this work?

DangerousDaveeg.xlsx
	A	B	C
59	Select Agent		Dates
60	Agent 1		05-Feb-22
61			26-Feb-22
62
63
Sheet1

Cell Formulas
Range	Formula
C60:C63	C60	=IFERROR(INDEX($B$1:$AE$1,AGGREGATE(15,6,COLUMN($B$1:$AE$1)-COLUMN($A$1)/((INDEX($B$2:$AE$55,MATCH($A$60,$A$2:$A$55,0),(COLUMN($B$1:$AE$1)-COLUMN($B$1)))=1)),ROW()-ROW($C$59))),"")

Cells with Data Validation
Cell	Allow	Criteria
A60	List	=$A$2:$A$55

 

[KOKOSEK]

Book1
	A	B	C	D	E	F	G	H	I
1	Name	05/02/2022	12/02/2022	19/02/2022	26/02/2022	05/03/2022	12/03/2022	19/03/2022	26/03/2022
2	Agent 1	1			1
3	Agent 2
4	Agent 3
5	Agent 4
6	Agent 5
7	Agent 6	1
8	Agent 7	1
9	Agent 8	1
10	Agent 9		1						1
11	Agent 10		1						1
59
60	Agent 1	05/02/2022 26/02/2022 28/05/2022 02/07/2022 30/07/2022
Sheet1

Cell Formulas
Range	Formula
B60	B60	=textjoinSUB(TEXT(IF(OFFSET($A$2:$A$55,MATCH($A$60,$A$2:$A$55,0)-1,1,1,COLUMNS($B$2:$AE$2))=1,$B$1:$AE$1,""),"dd/mm/yyyy")," ",0)
Press CTRL+SHIFT+ENTER to enter array formulas.

As on XL2013 there is no TEXTJOIN I've used UDF as substitute (to get result as you want to in format date & date)

Public Function textjoinSUB(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
'Created by TigerAvatar at www.excelforum.com, September 2012
'Purpose is to concatenate many strings into a single string
'Can be used with arrays, range objects, and collections
  
    Dim DataIndex As Variant    'Used to loop through arrays, range objects, and collections
    Dim strResult As String     'Used to build the result string
  
    'Test if varData is an Array, Range, or Collection
    If IsArray(varData) _
    Or TypeOf varData Is Range _
    Or TypeOf varData Is Collection Then
      
        'Found to be an, array, range object, or collection
        'Loop through each item in varData
        For Each DataIndex In varData
            'Check if the item isn't empty
            If Len(DataIndex) > 0 Then
                'Found the item isn't empty, check if user specified bUnique as True
                If bUnique = True Then
                    'bUnique is true, check if the item has been included in the result yet
                    If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                        'Item has not been included in the result, add item to the result
                        strResult = strResult & "||" & DataIndex
                    End If
                Else
                    'bUnique is not true, add item to the result
                    strResult = strResult & "||" & DataIndex
                End If
            End If
        Next DataIndex
      
        'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
      
    Else
        'Found not to be an array, range object, or collection
        'Simply set the result = varData
        strResult = varData
    End If
  
    'Output result
    textjoinSUB = strResult
  
End Function

 

[dangerousdaveeg]

H

Does this work?

DangerousDaveeg.xlsx
	A	B	C
59	Select Agent		Dates
60	Agent 1		05-Feb-22
61			26-Feb-22
62
63
Sheet1

Cell Formulas
Range	Formula
C60:C63	C60	=IFERROR(INDEX($B$1:$AE$1,AGGREGATE(15,6,COLUMN($B$1:$AE$1)-COLUMN($A$1)/((INDEX($B$2:$AE$55,MATCH($A$60,$A$2:$A$55,0),(COLUMN($B$1:$AE$1)-COLUMN($B$1)))=1)),ROW()-ROW($C$59))),"")

Cells with Data Validation
Cell	Allow	Criteria
A60	List	=$A$2:$A$55

This works exactly as required, I now just need to tweak it as it has come to light that some users are only going to use it in google sheets and that does not support the Aggregate function, unfortunately.

Thanks for the help

 

[dangerousdaveeg]

Book1
	A	B	C	D	E	F	G	H	I
1	Name	05/02/2022	12/02/2022	19/02/2022	26/02/2022	05/03/2022	12/03/2022	19/03/2022	26/03/2022
2	Agent 1	1			1
3	Agent 2
4	Agent 3
5	Agent 4
6	Agent 5
7	Agent 6	1
8	Agent 7	1
9	Agent 8	1
10	Agent 9		1						1
11	Agent 10		1						1
59
60	Agent 1	05/02/2022 26/02/2022 28/05/2022 02/07/2022 30/07/2022
Sheet1

Cell Formulas
Range	Formula
B60	B60	=textjoinSUB(TEXT(IF(OFFSET($A$2:$A$55,MATCH($A$60,$A$2:$A$55,0)-1,1,1,COLUMNS($B$2:$AE$2))=1,$B$1:$AE$1,""),"dd/mm/yyyy")," ",0)
Press CTRL+SHIFT+ENTER to enter array formulas.

As on XL2013 there is no TEXTJOIN I've used UDF as substitute (to get result as you want to in format date & date)

Public Function textjoinSUB(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
'Created by TigerAvatar at www.excelforum.com, September 2012
'Purpose is to concatenate many strings into a single string
'Can be used with arrays, range objects, and collections
 
    Dim DataIndex As Variant    'Used to loop through arrays, range objects, and collections
    Dim strResult As String     'Used to build the result string
 
    'Test if varData is an Array, Range, or Collection
    If IsArray(varData) _
    Or TypeOf varData Is Range _
    Or TypeOf varData Is Collection Then
     
        'Found to be an, array, range object, or collection
        'Loop through each item in varData
        For Each DataIndex In varData
            'Check if the item isn't empty
            If Len(DataIndex) > 0 Then
                'Found the item isn't empty, check if user specified bUnique as True
                If bUnique = True Then
                    'bUnique is true, check if the item has been included in the result yet
                    If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                        'Item has not been included in the result, add item to the result
                        strResult = strResult & "||" & DataIndex
                    End If
                Else
                    'bUnique is not true, add item to the result
                    strResult = strResult & "||" & DataIndex
                End If
            End If
        Next DataIndex
     
        'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
     
    Else
        'Found not to be an array, range object, or collection
        'Simply set the result = varData
        strResult = varData
    End If
 
    'Output result
    textjoinSUB = strResult
 
End Function

Thank you for your help, A formula based solution has been provided and I think for this scenario that may be the best option, but once again thank you for your time on this