Multiple text search in a cell

D

Doug

I am attempting to search data in a cell that has multiple options of text
(different countries). The problem is, I might have El Salvador but also have
a company in El Salvador. So I need to be able to pull the data for both. I
can pull El Salvador, but, can't figure out how to look for the other also.
Here's the formula I'm using to pull the data:

=IF(ISNUMBER(SEARCH("ELSALVADOR",'[Master File.xls]All Data'!G2)),'[Master
File.xls]All Data'!A2,"")

Can I extend this formula in some way to also pull the company data.

Here's the scenrio I have:

Column A Column G
name ELSALVADOR
name Australia
name Company
name Thailand

I need to pull the information in Column A anytime ELSALVADOR or Company
shows up and ignore the rest.


Thanks for any help in advance.
 
J

Jacob Skaria

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

Apply this formula and copy down to return all entries with this criteria
=IF(SUM(COUNTIF(B1:B50,{"company","Elsalvador"}))<ROW(A1),"",INDEX(A1:A50,
SMALL(IF(B1:B50={"company","Elsalvador"},ROW(A1:A50)),ROW(A1))))

If this post helps click Yes
 
P

Pete_UK

Try this:

=IF(ISNA(MATCH('[Master File.xls]All Data'!G2,{"ElSalvador","Company"},
0)),"",'[Master File.xls]All Data'!A2)

You can add more words inside the curly braces as required.

Hope this helps.

Pete
 
D

Doug

Hi Jacob,

Thanks for responding. I'm having an issue. I'm trying to do the array
CTRL+SHIFT+ENTER, but, it keeps saying "error in formula". This is what I've
typed so far:
=if(sum(countif('[Master File.xls]All Data'!$G$2:$G$17,

It's saying, I'm missing parenthesis. I tried just keying the {bracket and
the formula does not pick anything up.

Any help is appreciated



Jacob Skaria said:
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

Apply this formula and copy down to return all entries with this criteria
=IF(SUM(COUNTIF(B1:B50,{"company","Elsalvador"}))<ROW(A1),"",INDEX(A1:A50,
SMALL(IF(B1:B50={"company","Elsalvador"},ROW(A1:A50)),ROW(A1))))

If this post helps click Yes
---------------
Jacob Skaria


Doug said:
I am attempting to search data in a cell that has multiple options of text
(different countries). The problem is, I might have El Salvador but also have
a company in El Salvador. So I need to be able to pull the data for both. I
can pull El Salvador, but, can't figure out how to look for the other also.
Here's the formula I'm using to pull the data:

=IF(ISNUMBER(SEARCH("ELSALVADOR",'[Master File.xls]All Data'!G2)),'[Master
File.xls]All Data'!A2,"")

Can I extend this formula in some way to also pull the company data.

Here's the scenrio I have:

Column A Column G
name ELSALVADOR
name Australia
name Company
name Thailand

I need to pull the information in Column A anytime ELSALVADOR or Company
shows up and ignore the rest.


Thanks for any help in advance.
 
D

David Biddulph

Firstly, you don't key in the { for an array formula. Excel will add that
when you use Control Shift Enter to enter the formula.

But you need to have a complete formula before you enter it.
Your =if(sum(countif('[Master File.xls]All Data'!$G$2:$G$17,
is only the start of the formula. Your parentheses need to be in matching
pairs, and your functions need to be complete. IF needs at least 2 and
possibly 3 arguments, and so does COUNTIF. You can't get away with hitting
Enter, or Control Shift Enter, until you've finished your formula.

Perhaps you are getting confused between the {} which Excel puts around the
outside of the whole of array formula and the {} around the data array
{"company","Elsalvador"} in Jacob's formula. In the latter case, you do
type the { characters in. It is on;y at the end of the complete formula
that you use Control Shift Enter (instead of Enter) to put the formula into
Excel.
--
David Biddulph

Doug said:
Hi Jacob,

Thanks for responding. I'm having an issue. I'm trying to do the array
CTRL+SHIFT+ENTER, but, it keeps saying "error in formula". This is what
I've
typed so far:
=if(sum(countif('[Master File.xls]All Data'!$G$2:$G$17,

It's saying, I'm missing parenthesis. I tried just keying the {bracket and
the formula does not pick anything up.

Any help is appreciated



Jacob Skaria said:
Please note that this is an array formula. You create array formulas in
the
same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the
curly
braces at both ends like "{=<formula>}"

Apply this formula and copy down to return all entries with this criteria
=IF(SUM(COUNTIF(B1:B50,{"company","Elsalvador"}))<ROW(A1),"",INDEX(A1:A50,
SMALL(IF(B1:B50={"company","Elsalvador"},ROW(A1:A50)),ROW(A1))))

If this post helps click Yes
---------------
Jacob Skaria


Doug said:
I am attempting to search data in a cell that has multiple options of
text
(different countries). The problem is, I might have El Salvador but
also have
a company in El Salvador. So I need to be able to pull the data for
both. I
can pull El Salvador, but, can't figure out how to look for the other
also.
Here's the formula I'm using to pull the data:

=IF(ISNUMBER(SEARCH("ELSALVADOR",'[Master File.xls]All
Data'!G2)),'[Master
File.xls]All Data'!A2,"")

Can I extend this formula in some way to also pull the company data.

Here's the scenrio I have:

Column A Column G
name ELSALVADOR
name Australia
name Company
name Thailand

I need to pull the information in Column A anytime ELSALVADOR or
Company
shows up and ignore the rest.


Thanks for any help in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top