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

 

[bosco_yip]

In D2, copied down :

=LOOKUP(1,-FIND(C2,$A$2:$B$5),$B$2:$B$5)

Regards
Bosco

 

[Biz]

In D2, copied down :

=LOOKUP(1,-FIND(C2,$A$2:$B$5),$B$2:$B$5)

Regards
Bosco

Hi,
Tried your formula but it doesn't work.

I used formula below it works except it doesn't get the first match.
=LOOKUP(9.99999999999999E+307,SEARCH($E$2:$E$4,A2),$F$2:$F$4)

I believe I need to change lookup vale of 9.99999999999999E+307 but not sure to what.\

Kind Regards

Biz

 

[Sam_D_Ben]

I guess you are looking for this.

Excel 2013/2016
	A	B	C	D	E
1	Name	Group Name	Should Be	Search For	Return Value
2	Marvel (V) Ltd	Tom	Tom	(V)	Tom
3	Dr Strange (V) (C) Ltd	Tom	Tom	[C]	Harry
4	Black Panther [C] (C) Ltd	Harry	Harry	(C)	Mary
5	Spiderman (C) Ltd	Mary	Mary
Sheet1

Cell Formulas
Range	Formula
B2	{=INDEX($E$2:$E$4,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$4,A2)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

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

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

and run the following formula in B from B2 on downwards:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$D$2:$D$4&" "," "&$A2&" "),$E$2:$E$4)

If you are not on an American system, replace the decimal dot with a comma in the big number and replace the list separator comma with a semi-colon.

 

[Biz]

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

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

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

and run the following formula in B from B2 on downwards:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$D$2:$D$4&" "," "&$A2&" "),$E$2:$E$4)

If you are not on an American system, replace the decimal dot with a comma in the big number and replace the list separator comma with a semi-colon.

Hi Aladin,

Your proposed solution only works when I sort Column E in Descending Order.

Are there tips I can apply when to sort ascending or descending order?

Thank you very much for your help and prompt response.

Kind Regards

Biz

 

[Biz]

Hi Aladin,

Sorry my mistake I should sort Col E ascending but it fails for Black Panther [C] (C) Ltd where the answer should be Harry.

Do you know how to fix this problem?

Kind Regards

Biz

 

[Sam_D_Ben]

Its working for me. I guess sorting D:E together.

 

[Peter_SSs]

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.

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

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

Excel Workbook
	A	B	C	D	E
1	Name	Group Name	Should Be	Search For	Return Value
2	Marvel (V) Ltd	#"),"(","#("),")",")#"),"#",REPT(" ",100)),100,100)),D$2:E$4,2,0)]Tom	Tom	(V)	Tom
3	Dr Strange (V) (C) Ltd	Tom	Tom	[CKL]	Harry
4	Black Panther [CKL] (C) Ltd	Harry	Harry	(C)	Mary
5	Spiderman (C) [CKL] Ltd	Mary	Mary
Group Name (2)

 

[Aladin Akyurek]

Hi Aladin,

Sorry my mistake I should sort Col E ascending but it fails for Black Panther [C] (C) Ltd where the answer should be Harry.

Do you know how to fix this problem?

Kind Regards

Biz

Its working for me. I guess sorting D:E together.

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