Find a text value in a list

Posted by Rick on June 01, 2001 12:59 PM

I'm trying to look for a text value in a list and return a 1 if it's there, 0 if it's not. I'm doing something wrong!!

thanks for any help

Posted by Russell on June 01, 2001 1:01 PM

What kind of list?

Posted by Rick on June 01, 2001 1:06 PM

Re: What kind of list?

OK - there's 2 worksheets - one with registration names, one with people who completed a course 91500 names). I'm checking each registered name to see if it appears on the completed list. If so, the registration sheet needs to indicate a "1" or a "yes" or "completed" on it.

Hope that clarifies! Thanks

Posted by Russell on June 01, 2001 1:33 PM

Re: What kind of list?

Just use VLOOKUP. So let's say you have the following on your "registered" sheet:

Name
Smith, Bill
Jones, Sam
Johnson, Eileen

and on the completed sheet:

Johnson, Eileen

Ok, so if the first sheet has the names in column A, then just put a VLOOKUP formula in column B like so:

=IF(ISNA(VLOOKUP(A1, Sheet2!$A$1:$A$33000, 1, FALSE)), "", "Completed")

Where A1 is the column to the left of B1 that has the registered name, and the "Sheet2!$A$1:$A$33000" is the area on sheet2 containing the names of people that have completed the course.

Vlookup will return #N/A if it cannot find the value (so in this case I just had it return an empty string). Make sense? If not, email me...

Also, when you are putting in the range for it to look up, make sure you use absolute references (the dollar signs).

Posted by Aladin Akyurek on June 01, 2001 1:41 PM

Re: What kind of list?

Select all of the cells of the completed list and name the selected range, e.g., FULLLIST via the Name Box or Inser|Name|Define.

Go the first cell of the registered names (assuming these names to start in cell A2) and activate the cell next to it (this will be then B2).

In B2 enter: =ISNUMBER(MATCH(A2,FULLLIST,0))+0

Copy down this formula as far as needed.

Aladin

===========

Posted by Rick on June 01, 2001 5:43 PM

Re: What kind of list?

Thanks again for the help - I will try it tonight (OK... maybe in the morning). Thanks for the help!

But can you explain the ISNA part of the formula? And maybe the rest? It looks like an IF function and I assume ISNA means that the result is N/A, but I admit I don't understand.

Posted by Rick on June 01, 2001 5:44 PM

Re: What kind of list?

Thanks - I will try it. I appreciate your help.

Posted by Russell on June 02, 2001 4:21 PM

Re: What kind of list?

Yes, it returns true if what is passed to it returns #N/A. When Vlookup cannot find a match, it returns #N/A.