Search for a value in multiple non-adjacent columns and retrieve an array with all values of the 1st column where there was a match in that row.

[Solvap]

Hello Everyone,

I hope someone can help me out on this one. It's been a week now and I still cannot get it to work properly.

Suppose the following data-set (dummy data)

Needed values	Column1	Column2	Column3	Column4	Column5	Column6
AtRow01	ID-38-86	email	ID-06-936	phone	ID-29-381	email1
AtRow02	ID-47-109	et@yahoo.org	ID-24-601	1-673-487-3587	ID-42-488	gravida.mauris@google.ca
AtRow03	ID-84-277	euismod.est@protonmail.net	ID-95-666	(725) 482-8987	ID-03-981	vitae@outlook.ca
AtRow04	ID-03-651	felis.purus@outlook.ca	ID-62-987	1-308-684-8517	ID-79-966	at.fringilla@yahoo.ca
AtRow05	ID-82-284	tempor.lorem.eget@protonmail.org	ID-94-808	1-811-323-7166	ID-80-429	est.vitae@icloud.com
AtRow06	ID-62-738	leo.cras.vehicula@aol.ca	ID-04-612	(409) 724-7091	ID-02-488	eu.placerat.eget@yahoo.org
AtRow07	ID-71-278	massa.vestibulum@google.couk	ID-82-731	(482) 777-6082	ID-12-799	pharetra.nibh@hotmail.edu
AtRow08	ID-29-418	etiam@icloud.couk	ID-49-887	1-363-761-5973	ID-77-275	hendrerit.neque@yahoo.ca
AtRow09	ID-22-636	magna.nam@hotmail.couk	ID-19-225	1-984-889-6786	ID-04-330	velit@yahoo.couk
AtRow10	ID-29-299	augue.malesuada@google.couk	ID-72-252	(458) 436-3484	ID-78-14	eget.nisi@aol.edu

What I'm looking for is to "TextJoin" all Needed values, sorted, where the 5 first characters of an ID matched in any column as here-after:

A	B	C
IDs	What I am looking for	What I get with my current formula
ID-02	AtRow06	AtRow06
ID-03	AtRow03, AtRow04	AtRow04, AtRow03
ID-04	AtRow09	AtRow06, AtRow09
ID-12	AtRow07	AtRow07
ID-19	AtRow09	AtRow09
ID-24	AtRow02	AtRow02
ID-29	AtRow01, AtRow08, AtRow10	AtRow08, AtRow10, AtRow01

As you can see, the string "ID-02" is only found in Column5 and the value in the 1st column, "AtRow06", is retrieved.
"ID-03" is found 2 times, in Column5 so we retrieve AtRow03 on the same row from 1st column and in Column1 so we retrieve AtRow04 on the same row from 1st column.

I've come to the following function, which seems to work but I can't get it to be sort each value. So far, I get unsorted values "AtRow04, AtRow03" instead of "AtRow03, AtRow04".

C1=TEXTJOIN(", ",TRUE,FILTER($A$2:$A$11,LEFT($B$2:$B$11,5)=I2,""),FILTER($A$2:$A$11,LEFT($D$2:$D$11,5)=I2,""),FILTER($A$2:$A$11,LEFT($F$2:$F$11,5)=I2,""))
C2=TEXTJOIN(", ",TRUE,FILTER($A$2:$A$11,LEFT($B$2:$B$11,5)=I3,""),FILTER($A$2:$A$11,LEFT($D$2:$D$11,5)=I3,""),FILTER($A$2:$A$11,LEFT($F$2:$F$11,5)=I3,""))
and so on.

I'm also wondering if there is a way to search for a specific value in multiple columns and, when there is a match, put the left most values (from 1st column) into an array, then look for the following match and so on ?


Btw, I'm using Excel from Office 365 and I'm not allowed to use Excel Table, Named ranges nor VBA ?

Any help is more than welcome.
Thanks in advance.
Solvap

 

[Eric W]

How about:

=TEXTJOIN(", ",TRUE,SORT(FILTER(A$2:A$11,MMULT(--(LEFT(B$2:F$11,5)=I2),{1;0;1;0;1}))))

 

[Solvap]

TEXTJOIN(", ",TRUE,SORT(FILTER(A$2:A$11,MMULT(--(LEFT(B$2:F$11,5)=I2),{1;0;1;0;1}))))

Hello Eric W,

Thank you soooo much. It's the first time I see that MMULT function. I'll dig into it, very interesting
It works super

Thank you again, I will finally get some time to sleep
Solvap

 

[Eric W]

Happy to help! Thanks for the update, and get some sleep! ?