Multiple text search in a cell

  • Thread starter Thread starter Doug
  • Start date Start date
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.
 
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
 
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
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top