Excel Multiple Search and return Non Array formula

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
Platform
  1. Windows
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

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

<tbody>
</tbody>

Your help would be greatly appreciated.

Kind Regards

Biz
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
In D2, copied down :

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

Regards
Bosco
 
Upvote 0
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
 
Upvote 0
I guess you are looking for this.


Excel 2013/2016
ABCDE
1NameGroup NameShould BeSearch ForReturn Value
2Marvel (V) LtdTomTom(V)Tom
3Dr Strange (V) (C) LtdTomTom[C]Harry
4Black Panther [C] (C) LtdHarryHarry(C)Mary
5Spiderman (C) LtdMaryMary
Sheet1
Cell Formulas
RangeFormula
B2{=INDEX($E$2:$E$4,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$4,A2)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Sort D:E in ascending order on column E...


Book1
ABCDE
1NameGroup NameShould BeSearch ForReturn Value
2Marvel (V) LtdTomTom(C)Mary
3Dr Strange (V) (C) LtdTomTom(V)Tom
4Black Panther [C] (C) LtdHarryHarry[C]Harry
5Spiderman (C) LtdMaryMary
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.
 
Upvote 0
Sort D:E in ascending order on column E...

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

<tbody>
</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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
ABCDE
1NameGroup NameShould BeSearch ForReturn Value
2Marvel (V) LtdTomTom(V)Tom
3Dr Strange (V) (C) LtdTomTom[C]Harry
4Black Panther [C] (C) LtdHarryHarry(C)Mary
5Spiderman (C) LtdMaryMary
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
ABCDE
1NameGroup NameShould BeSearch ForReturn Value
2Marvel (V) Ltd#&quot;),&quot;(&quot;,&quot;#(&quot;),&quot;)&quot;,&quot;)#&quot;),&quot;#&quot;,REPT(&quot; &quot;,100)),100,100)),D$2:E$4,2,0)]TomTom(V)Tom
3Dr Strange (V) (C) LtdTomTom[CKL]Harry
4Black Panther [CKL] (C) LtdHarryHarry(C)Mary
5Spiderman (C) [CKL] LtdMaryMary
Group Name (2)
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top