Excel Multiple Search and return Non Array formula

[Biz]

Hi,
I have difficult question which I need non array formula in column B which looks at column A reference uses wildcard match Column an returns vales from Column E.


Column A Column B Column C Column D Column E

[TABLE="width: 370"]
<tbody>[TR]
[TD="width: 150, bgcolor: transparent"]Name
[/TD]
[TD="width: 82, bgcolor: transparent"]Group Name
[/TD]
[TD="width: 64, bgcolor: transparent"]Should Be
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent"]Search For
[/TD]
[TD="width: 64, bgcolor: transparent"]Return Value
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Marvel (V) Ltd
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Tom
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"](V)
[/TD]
[TD="bgcolor: transparent"]Tom
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dr Strange (V) (C) Ltd
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Tom
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][C]
[/TD]
[TD="bgcolor: transparent"]Harry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Black Panther [C] (C) Ltd
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Harry
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"](C)
[/TD]
[TD="bgcolor: transparent"]Mary
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Spiderman (C) Ltd
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Mary
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Your help would be greatly appreciated.

Kind Regards

Biz

 

[Biz]

That's right. Sort D:E together, ascending on D.

Thank you vey much, it works as desired.

 

[Biz]

Why do you specify a non array formula? Surely any formula that performs the task efficiently should be acceptable?

Anyway, ..

IF your sample data is representative in that all 'Search For' items are 3 characters long you could try this - no sorting required.

Group Name (1)

	A	B	C	D	E
Name	Group Name	Should Be	Search For	Return Value
Marvel (V) Ltd	Tom	Tom	(V)	Tom
Dr Strange (V) (C) Ltd	Tom	Tom	[C]	Harry
Black Panther [C] (C) Ltd	Harry	Harry	(C)	Mary
Spiderman (C) Ltd	Mary	Mary

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 169px;"><col style="width: 93px;"><col style="width: 79px;"><col style="width: 83px;"><col style="width: 95px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =VLOOKUP(MID(A2,AGGREGATE(15,6,SEARCH(D$2:D$4,A2),1),3),D$2:E$4,2,0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


IF your sample data is representative in that all 'Search For' items are enclosed in () or [] then you could try this.

Group Name (2)

	A	B	C	D	E
Name	Group Name	Should Be	Search For	Return Value
Marvel (V) Ltd	Tom	Tom	(V)	Tom
Dr Strange (V) (C) Ltd	Tom	Tom	[CKL]	Harry
Black Panther [CKL] (C) Ltd	Harry	Harry	(C)	Mary
Spiderman (C) [CKL] Ltd	Mary	Mary

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 185px;"><col style="width: 93px;"><col style="width: 79px;"><col style="width: 83px;"><col style="width: 95px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =VLOOKUP(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"[","#["),"]","]#"),"(","#("),")",")#"),"#",REPT(" ",100)),100,100)),D$2:E$4,2,0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Can't use an array formula as people I'm setting up formluas don't like it.
I like Group Name 1 formula with aggregate formula is awesome.

Thank you for your help too.

 

[Peter_SSs]

That's right. Sort D:E together, ascending on D.

Ahh, now that matches your screen shot from post 5, whereas your description then didn't

Sort D:E in ascending order on column E...

Thank you vey much, it works as desired.

In that case I must have misunderstood what you meant by "get the first match" in post 3. I thought you meant the first match in the column A value but you must have meant the first match in the column D values. Is that correct? Just to confirm, should both of these give "Harry" as the result?
Black Panther [C] (C) Ltd
Black Panther (C) [C] Ltd

 

[Biz]

Ahh, now that matches your screen shot from post 5, whereas your description then didn't


In that case I must have misunderstood what you meant by "get the first match" in post 3. I thought you meant the first match in the column A value but you must have meant the first match in the column D values. Is that correct? Just to confirm, should both of these give "Harry" as the result?
Black Panther [C] (C) Ltd
Black Panther (C) [C] Ltd

Hi M8,

Your formula with Vlookup with AGGREGATE function derives the results which fit my case.

[TABLE="width: 164"]
<colgroup><col width="154" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5489;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 154, bgcolor: transparent"]Black Panther [C] (C) Ltd[/TD]
[TD="width: 64, bgcolor: #C6E0B4"]Harry[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Black Panther (C) [C] Ltd [/TD]
[TD="width: 64, bgcolor: #C6E0B4"]Mary[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much.

Kind Regards

Biz

 

[Peter_SSs]

Thank you vey much, it works as desired.

Your formula with Vlookup with AGGREGATE function derives the results which fit my case.

The two above statements cannot both be true.

Excel Workbook
	A	B	D	E
1	Name	Group Name	Search For	Return Value
2	Marvel (V) Ltd	Tom	(C)	Mary
3	Dr Strange (V) (C) Ltd	Tom	(V)	Tom
4	Black Panther [C] (C) Ltd	Harry	[C]	Harry
5	Black Panther (C) [C] Ltd	Harry
Group Name (Post 5)

Excel Workbook
	A	B	D	E
1	Name	Group Name Post 9	Search For	Return Value
2	Marvel (V) Ltd	Tom	(V)	Tom
3	Dr Strange (V) (C) Ltd	Tom	[C]	Harry
4	Black Panther [C] (C) Ltd	Harry	(C)	Mary
5	Black Panther (C) [C] Ltd	Mary
Group Name (Post 9)

 

[Biz]

Hi Peter,

Your Vlookup and Aggregate formula is correct and gives the right result I'mafter.
Thank you for your amazing formula and I spent looking at it and learnt new tricks.

Kind Regards,
Biz

 

[Peter_SSs]

You're welcome. Thanks for the clarification.

BTW, if
- the only () or [] in column A are around the 'Search for' terms, and
- the 'Search for' terms are all exactly 3 characters
then a further simplification is possible

Excel Workbook
	A	B	C	D	E
1	Name	Group Name		Search For	Return Value
2	Marvel (V) Ltd	Tom		(V)	Tom
3	Dr Strange (V) (C) Ltd	Tom		[C]	Harry
4	Black Panther [C] (C) Ltd	Harry		(C)	Mary
5	Black Panther (C) [C] Ltd	Mary
6	Spiderman (C) Ltd	Mary
Group Name (1a)

 

[Biz]

Awesome streamlined formula. Thank you very much for your help again.