Pull List From Multiple Columns

[downwithjunk]

I'm trying to pull a dataset from a bulk set based on a cell value. I usually do this with an index match; however, now I'm needing to get it based out of multiple columns which I'm not able to figure out. Below is a sample of the setup. I would like to have a dynamic list (A12:B14) based on the value in A9. But it would need to include if Apples appears in column C, D, or E. Any help would be greatly appreciated.

	A	B	C	D	E
1	FIRST	LAST	TAG1	TAG2	TAG3
2	Bill	Smith	Apples
3	Todd	Black	Oranges
4	Brett	Harris	Pears	Apples
5	Mary	White	Pears	Oranges	Grapes
6	Jacob	Green	Grapes	Pears	Apples
7
8	PRODUCT
9	Apples
10
11	LIST
12	Bill	Smith
13	Brett	Harris
14	Jacob	Green

 

[JLGWhiz]

If you don't mind using vba, here is a macro that will create the list. the code assumes you would want to remove existing list, if any and create a new one based on criteria.

Sub t()
Dim fn As Range, crt As Variant, adr As String
With ActiveSheet
    .Range("A12:B12", .Range("A12:B12").End(xlDown)).ClearContents
    crt = .Range("A9").Value
    Set fn = Intersect(.UsedRange, .Range("C:E")).Find(crt, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            adr = fn.Address
            Do
                If Range("A12") = "" Then
                    .Cells(fn.Row, 1).Resize(, 2).Copy .Range("A12")
                Else
                    .Cells(fn.Row, 1).Resize(, 2).Copy .Cells(Rows.Count, 1).End(xlUp)(2)
                End If
                Set fn = Intersect(.UsedRange, .Range("C:E")).FindNext(fn)
            Loop While fn.Address <> adr
        End If
End With
End Sub

Run the code from the standard code module1.

 

[downwithjunk]

Thanks. I'm trying to stick with a formula if at all possible.

 

[Peter_SSs]

1. 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’)
   
   
2. Also have a look at XL2BB as a better way to post your sample data and expected results.

I'm trying to stick with a formula if at all possible.

Can we use a helper column (which could be hidden)?

Because of point 1 above I'm not sure which of these will be best for you.

If you have Excel 365 with the FILTER function then all you need is to put the formula shown into cell A12 only and the other results will automatically 'spill' into the other required cells.

downwithjunk 2020-04-16 1.xlsm
	A	B	C	D	E	F
1	FIRST	LAST	TAG1	TAG2	TAG3	All Tags
2	Bill	Smith	Apples			|Apples|
3	Todd	Black	Oranges			|Oranges|
4	Brett	Harris	Pears	Apples		|Pears|Apples|
5	Mary	White	Pears	Oranges	Grapes	|Pears|Oranges|Grapes|
6	Jacob	Green	Grapes	Pears	Apples	|Grapes|Pears|Apples|
7
8	PRODUCT
9	Apples
10
11	LIST
12	Bill	Smith
13	Brett	Harris
14	Jacob	Green
15
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	="|"&TEXTJOIN("|",1,C2:E2)&"|"
A12:B14	A12	=FILTER(A2:B6,ISNUMBER(SEARCH("|"&A9&"|",F2:F6)))
Dynamic array formulas.

If you have Excel 2010 or later then try these formulas in row 12, copied down as far as you might need.

downwithjunk 2020-04-16 1.xlsm
	A	B	C	D	E	F
1	FIRST	LAST	TAG1	TAG2	TAG3	All Tags
2	Bill	Smith	Apples			|Apples|
3	Todd	Black	Oranges			|Oranges|
4	Brett	Harris	Pears	Apples		|Pears|Apples|
5	Mary	White	Pears	Oranges	Grapes	|Pears|Oranges|Grapes|
6	Jacob	Green	Grapes	Pears	Apples	|Grapes|Pears|Apples|
7
8	PRODUCT
9	Apples
10
11	LIST
12	Bill	Smith
13	Brett	Harris
14	Jacob	Green
15
16
Sheet2

Cell Formulas
Range	Formula
F2:F6	F2	="|"&TEXTJOIN("|",1,C2:E2)&"|"
A12:A16	A12	=IFERROR(INDEX(A$2:A$6,AGGREGATE(15,6,(ROW(A$2:A$6)-ROW(A$2)+1)/ISNUMBER(SEARCH("|"&$A$9&"|",$F$2:$F$6)),ROWS(A$12:A12))),"")
B12:B16	B12	=IF(A12="","",INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/ISNUMBER(SEARCH("|"&$A$9&"|",$F$2:$F$6)),ROWS(A$12:A12))))