Excel question

[ArcticHeron]

To pose a question that probably has a very obvious answer, how to I search for a group of identifiers on a single excel tab?

Up until now I've been going one at a time, cross matching one identifier to a larger spreadsheet. Is there a way for me to search for a large group of names at a time?

I used to use the duplicate value checking in formatting, but that requires setting up a new tab and then transferring the information back which sort of defeats the purpose.

 

[Greboth]

Assuming your searching for your identifier as a whole and not searching for the identifier as only part of a cell, there's couple of easy methods.

The first is COUNTIF which will return the number of times your identifier appears on the data list
=COUNTIF(data range, your identifier cell)

That's the quick and dirty method, if you want something a little neater so it's a yes or no, is just adding an IF statement to the count if

=IF(COUNTIF(data range, your identifier cell)>0,"Yes","No")

This simply checks if the count is greater than 0, if it is then it doesn't matter whether the identifier appears once or 1,000 times, the formula just outputs "yes". If the formula is 0, so the identifier isn't found, it returns "No".

The other option for finding an identifier is MATCH, this searches in a similar way to the COUNTIF but returns the position of the match in the list. This is useful if you include the full column in your data range as it will return the row number that your identifier is on.

=MATCH(your identifier cell, data range, 0)

The 0 at the end is for an exact match, this can be changed for less than or greater than, depending on the data you're looking for.

If the identifier you're looking for is only part of a cell in your data list, things get more complicated and off the top of my head I don't know but if this is the case, I can look in to it or someone else might know the answer off the top of their head.