Multiple text search in a cell
 

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.

Multiple text search in a cell
 

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:A5 0)),ROW(A1))))

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


"Doug" wrote:

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.

Multiple text search in a cell
 

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


On Nov 10, 3:14*pm, Doug wrote:
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.

Multiple text search in a cell
 

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" wrote:

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:A5 0)),ROW(A1))))

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


"Doug" wrote:

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.

Multiple text search in a cell
 

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" wrote in message
...
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" wrote:

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:A5 0)),ROW(A1))))

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


"Doug" wrote:

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.