If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|