Searching for string in variable array

hypedaj

New Member
Joined
Feb 17, 2009
Messages
2
Hi all:
Thanks in advance for your help.

I have the following issue:
I'm trying to search for string "Intel" in an array A1:A100
However, in the array, "Intel" doesn't always appear as "Intel", sometimes it's "abcIntel" or "Intelproject1" or " Intel xyz"

I've used the address function and tried to combine it with the FIND/Search function, but with no success.

Any thoughts on how to set this up without VBA?

Thanks!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi all:
Thanks in advance for your help.

I have the following issue:
I'm trying to search for string "Intel" in an array A1:A100
However, in the array, "Intel" doesn't always appear as "Intel", sometimes it's "abcIntel" or "Intelproject1" or " Intel xyz"

I've used the address function and tried to combine it with the FIND/Search function, but with no success.

Any thoughts on how to set this up without VBA?

Thanks!

Try, for example...

=LOOKUP(9.99999999999999E+307,SEARCH(E1,$A$1:$A$100),$A$1:$A$100)

where E1 houses a search value like Intel.
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
With
A1:A100 containing a list of text values (blanks allowed)
and
C1: (a text value to find in that list.....eg Intel)

This formula returns the contents of the list cell containing the C1 text
(or "no match")
Code:
B1: =IF(COUNTIF(A1:A100,"*"&C1&"*"),INDEX(A1:A100,
MATCH("*"&C1&"*",A1:A100,0)),"no match")
Is that something you can work with?
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,132
Office Version
  1. 365
Platform
  1. Windows
Aladin's method will find the last match.

These methods find the first match, not case sensitive. I prefer method 2 or like Ron's method 3.
=INDIRECT(ADDRESS(MATCH("*" & E1 & "*",$A$1:$A$100,0),1))
=OFFSET($A$4, MATCH("*" & E1 & "*",$A$4:$A$100,0)-1,0)
=INDEX(A1:A100,MATCH("*" & E1 & "*",A1:A100,0),1)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,131
Members
414,505
Latest member
quoctrungvu99

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
Top