FIND/SEARCH FORMULA IN MS EXCEL (For range of data)

ZAKIR

New Member
Joined
Sep 17, 2014
Messages
2
HI,

I am looking for find /search formula in Ms Excel, in which i can find range of data from a cell.

for example Syntax are:

=find(find_text,within_text,start number)

=search(find_text,within_text,start number)


In syntax "find_text" should be range of data instead of single cell/or criteria.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

You could use an array formula.

For example, if the cell you want to search in is A1 then you could enter this array formula into cells B1 and C1 (by Ctrl + Shift + Enter):

{=SEARCH({"apple","banana"},A1)}
 

ZAKIR

New Member
Joined
Sep 17, 2014
Messages
2
Thanks gpeacock!

{=SEARCH({"apple","banana"},A1)}

But i need to put Range of Data (A2:A180) instead of "apple","banana", because i have more than 150 text values to be search from 1 cell.




 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,193
You Can try

=SUMPRODUCT(--(A2:A180=A1),ROW(A2:A180))

where A1 is your data to be searched. If found returns 0 else returns row number.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,047
Messages
5,526,464
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top