Help searching for strings in 2D array and returning multiple results

[Sparhan]

Hi,

I have some data that looks something like this:

5/9/23​	Apple	Banana	Banana
6/9/23​	Banana	Grape	Grape
7/9/23​	Orange	Apple	Watermelon
8/9/23​	Grape	Banana	Mango
9/9/23​	Mango	Mango	Apple

Basically I want to write a formula that finds all the occurances of 'Apple' within B1:D5 and then returns the corresponding date from column A . So the results would output like this:

5/9/23​

7/9/23​

9/9/23​

If it was looking for 'Mango' it would ideally return this (only reading each row once, thereby not duplicating the date):

8/9/23​

9/9/23​

I believe this is an array formula I need, but I just cannot work out how to get it to work. TIA!

 

[shift-del]

Hello Sparhan

Mappe10
	F
1	08.09.2023
2	09.09.2023
3
4
Tabelle1

Cell Formulas
Range	Formula
F1:F2	F1	=LET( data,TOCOL(A1:A5/(B1:D5="Mango")), numbers,ISNUMBER(data), result,UNIQUE(FILTER(data,numbers)), result)
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	05/09/2023	Apple	Banana	Banana
2	06/09/2023	Banana	Grape	Grape
3	07/09/2023	Orange	Apple	Watermelon
4	08/09/2023	Grape	Banana	Mango
5	09/09/2023	Mango	Mango	Apple
6
7
8	Apple	05/09/2023
9		07/09/2023
10		09/09/2023
Data

Cell Formulas
Range	Formula
B8:B10	B8	=TOCOL(IF(B1:D5=A8,A1:A5,1/0),2)
Dynamic array formulas.

 

[Sparhan]

Hello Sparhan

Mappe10
	F
1	08.09.2023
2	09.09.2023
3
4
Tabelle1

Cell Formulas
Range	Formula
F1:F2	F1	=LET( data,TOCOL(A1:A5/(B1:D5="Mango")), numbers,ISNUMBER(data), result,UNIQUE(FILTER(data,numbers)), result)
Dynamic array formulas.

Hi @shift-del, this worked perfectly, thank you!

Is there a way at all of making it able to find results even if the string isn't an exact match, for example below if I wanted to find all "Mango" again and have it recognise the data in D2 as well as the others?

5/9/23	Apple	Banana	Banana
6/9/23	Banana	Grape	Mango and Banana
7/9/23	Orange	Apple	Watermelon
8/9/23	Grape	Banana	Mango
9/9/23	Mango	Mango	Apple

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	05/09/2023	Apple	Banana	Banana
2	06/09/2023	Banana	Grape	Mango and Banana
3	07/09/2023	Orange	Apple	Watermelon
4	08/09/2023	Grape	Banana	Mango
5	09/09/2023	Mango	Mango	Apple
6
7
8	Mango	06/09/2023
9		08/09/2023
10		09/09/2023
11		09/09/2023
Data

Cell Formulas
Range	Formula
B8:B11	B8	=TOCOL(IF(ISNUMBER(SEARCH(" "&A8&" "," "&B1:D5&" ")),A1:A5,1/0),2)
Dynamic array formulas.

 

[Sparhan]

How about

Fluff.xlsm
	A	B	C	D
1	05/09/2023	Apple	Banana	Banana
2	06/09/2023	Banana	Grape	Mango and Banana
3	07/09/2023	Orange	Apple	Watermelon
4	08/09/2023	Grape	Banana	Mango
5	09/09/2023	Mango	Mango	Apple
6
7
8	Mango	06/09/2023
9		08/09/2023
10		09/09/2023
11		09/09/2023
Data

Cell Formulas
Range	Formula
B8:B11	B8	=TOCOL(IF(ISNUMBER(SEARCH(" "&A8&" "," "&B1:D5&" ")),A1:A5,1/0),2)
Dynamic array formulas.

Hi @Fluff, thanks for replying, this would work for the string search but it returns multiple results for 9/9/23. Is there anyway to incorporate the UNIQUE/FILTER function that shift-del did in their reply so that we only get one result returned for 9/9/23?

 

[Fluff]

Yup, you can use

=UNIQUE(TOCOL(IF(ISNUMBER(SEARCH(" "&A8&" "," "&B1:D5&" ")),A1:A5,1/0),2))

 

[Sparhan]

Hi,

Thanks for the help so far, been great. I have a follow up query, I have some data that looks like this:

5/9/23	Apple	Banana	Banana
6/9/23	Banana	Grape	Grape
7/9/23	Orange	Apple x2	Watermelon
8/9/23	Grape	Banana	Mango
9/9/23	Mango	Mango	Apple x3

So I am currently using @Fluff's formula to find the search word and it returns the 3 dates. Is it possible to get it to also look at the 'x2' or 'x3' within the cell and give the date that many times. So for example if searching for 'Apple' in this table it would return:

5/9/23
7/9/23
7/9/23
9/9/23
9/9/23
9/9/23

I have a formula that gets it to look and pull out the number from the cell but don't know how to get it to duplicate the result that many times. Thanks!

 

[Fluff]

As this is a significantly different question, it needs a new thread. Thanks