Previous Topic Next Topic

Search a column against a column of text

Solved/Closed
angel - Jun 16, 2010 at 11:01 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 20, 2010 at 08:49 PM
Hi, I've been stumped with this problem for 2 days now and would appreciate any help.

I have column A and column B. Column A has about 20,000 rows and Column B has 10 rows. Both columns have text values. I want to search Column A using Column B as criteria. So if any of the text values in Column B appears in Column A, I want to return a value of 1.


This is an example: If Lion, Tiger or dog appears in column A, it should return 1.

ColA ColB ColC
cat lion 0
dog tiger 1
bird dog 0
lion 1
tiger 1




Any help would be really appreciated. Thanks!


Related:

4 responses

Answer 1 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 16, 2010 at 12:26 PM
You can use either vlookup or match

=IF(ISERROR(MATCH(B2, A:A,0)),0,1)

if match is not found in A, it will show 0
if a match is found in A, it will show 1
1
angel
Jun 16, 2010 at 04:02 PM
thanks! that works!

what if i have several categories? for example: if anything falls under Lion, Tiger or Dog, it should return "Category 1". And if it falls under Bird or Rat, it should show "Category 2", and if it falls under Cat or Snake, it should show "Category 3"?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 16, 2010 at 06:26 PM
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
0
angel
Jun 17, 2010 at 02:35 AM
here's the sample workbook: http://www.editgrid.com/user/oohangel/sample

Search Criteria = Categories sheet, column B
Search in = Data sheet, column A

Result:
- should show in Data sheet, column B
- should show Category Name found in Categories sheet, column A

Right now, I was able to make it work using only 1 category with your formula. Column B shows "Hardware" if column A has any of the search criteria under "Hardware".

But how do I also make it show "Software" or "Utility" if column A falls under those categories?

would appreciate any help you can give :)
0
angel
Jun 17, 2010 at 08:31 AM
I was able to make it work!

My only problem now is that some search criteria use wildcard (*). For example, the search criteria is

Corrected*Memory*Error*

MATCH does not understand wildcard and always returns false. I tried SEARCH, but it doesn't accept a range of criteria. Any suggestions?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 17, 2010 at 10:18 AM
You say you were able to make it work

So could you share your latest effort so one can see what changes to the data or formula is made and then go from there
0
Answer 2 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 18, 2010 at 10:15 PM
Ok you have to do one thing

For each error, you have to write the category next to it. Right now you have only once at the start of the group. Now you have to write for each error what sort of error is this. Then you can use this formula


=IF(ISERROR( LOOKUP(10000,SEARCH(V$2:V$27,A2),U$2:U$27)),"", U:ULOOKUP(10000,SEARCH(V$2:V$27,A2),U$2:U$27))
1
angel
Jun 20, 2010 at 01:48 PM
i tried it, but it's not giving the correct result. i parsed the formula, and this part gives an error:

" U:ULOOKUP(10000,SEARCH(V$2:V$27,A2),U$2:U$27)"
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 20, 2010 at 04:04 PM
Sorry for some reason copy and paste did not work and I did not even saw it
Correct formula is
=IF(ISERROR( LOOKUP(10000,SEARCH(W$2:W$27,A2),V$2:V$27)),"", LOOKUP(10000,SEARCH(W$2:W$27,A2),V$2:V$27))

See this book

https://authentification.site/files/23056232/sample1.xls
0
angel
Jun 20, 2010 at 04:11 PM
oh wow! that works perfectly! thank you so much! you helped me a lot as I have thousands of these records that needed to be categorized. thank you!
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 20, 2010 at 08:49 PM
welcome
0
Answer 3 / 4
Chris
Jun 16, 2010 at 11:06 AM
In which cell do you want the value 1 to display?
0
Answer 4 / 4
angel
Jun 17, 2010 at 07:00 AM
I was able to make it work! This is what I used:

=IF(ISERROR(MATCH($J2,'Wintel Report'!$AD$3:$AD$10,0)),IF(ISERROR(MATCH($J2,'Wintel Report'!$AD$12:$AD$20,0)),IF(ISERROR(MATCH($I2,'Wintel Report'!$AD$22:$AD$25,0)),IF(ISERROR(MATCH($I2,'Wintel Report'!$AD$26:$AD$27,0)),IF(ISERROR(MATCH($I2,'Wintel Report'!$AD$28:$AD$29,0)),IF(ISERROR(MATCH($I2,'Wintel Report'!$AD$30:$AD$46,0)),IF(ISERROR(MATCH($I2,'Wintel Report'!$AD$47:$AD$49,0)),IF(ISERROR(MATCH($I2,'Wintel Report'!$AD$50,0)),IF(ISERROR(MATCH($I2,'Wintel Report'!$AD$51,0)),"Non-issue","Citrix"),"Bug check"),"Backupexec"),"Performance"),"Storage Capacity"),"Server Capacity"),"Network / Printer"),"Internal Devices / Power"),"Drive Array / Cluster")



My only problem now is that some search criteria use wildcard (*). For example, the search criteria is

Corrected*Memory*Error*

MATCH does not understand wildcard and always returns false. I tried SEARCH, but it doesn't accept a range of criteria. Any suggestions?
0

Similar discussions

Auto generate serial number in excel based on Column
Raj_1562 -
TrowaD -

4 responses
Search in conversations on Messenger not possible anymore
GentseGast -
kiransaini88 -

3 responses