Excel Search Function

[openmind]

OK this is what I'm trying to do...

I have an excel workbook with two sheets.

The first sheet contains a list of usernames in column A along with other bumph in the rest of the columns. Column A has unique strings though for each row.

Sheet two has a much shorter list of usernames, again each one unique.

Now I know that each of the usernames in sheet 2 is in the much longer list in sheet 1 and I'm trying to figure out a formulae that simply says, if this cell is found in this column on sheet 2, output ye.

Tried the lookup, vlookup, match, find functions but either I'm doing something wrong or they won't have it because this aint working! ;)

Any ideas?

[Leo-InstallingIT]

I'm not an excel expert. But I would think the easiest way would be to use a MATCH combined with a ISNUMBER to return TRUE/FALSE

Something like ISNUMBER(MATCH(A1,Sheet1!A:A,0)) ?
Many Thanks

Leo

[spreadsheetsdirect]

You can use something like...


=IF(ISERROR(MATCH(A2,Sheet1!$A$2:$A$65000,0)),"","Y")


where A2 is the name to search for, the range is where to search. If no match it will leave the cell blank, if there is a match you'll get a Y.