PDA

View Full Version : Searching for text in a list



hudder
02-28-2006, 08:54 AM
This might be a very basic question, but I am stumped. I have a master list of about 5000 items that I am comparing to a smaller list of test results, looking for which of those 5000 items occur in the test results. If the item is in the test result I'd like the search to report a 1, if not a 0. I've been trying to combine an IF function with a VLOOKUP function, but I can't seem to get it to work.

I've attached the spreadsheet below. The master list is on the first worksheet and the test results are in the second. I'm trying to have the results for the test N1 reported next to the master list of items in a column named N1. There are more test results, so I'm trying to compile a sheet of 1's and 0's showing whether the item is in the test result, for all the tests.

Any help would be appreciated!

Mike

XLGibbs
02-28-2006, 10:52 AM
You could use a countIf formula for this..

=COUNTIF(Sheet2!$A$1:$A$5000,A1)

Where Sheet2!$A$1:$A$5000 is your list of 5000 items, and A1 contains your test results. You can just drag this formula down next to the test results. If the test result data appears in the 5000 list, it will return a 1, else zero.

I couldn't download your attachment at work, so hope that helps out.

hudder
02-28-2006, 12:05 PM
That's gread XLGibbs. That worked very well. Thanks for your help!

Mike