Looking up in a list (2007)

Topic

New Reply

I am having trouble with a formula to look in a list for a particular string.

I have a long list of names sorted in one column. In another column, I have a short list of names. I wold like to have some formula in the cell next to each name in the short list that will compere the short list name to the long list and tell me if that name exists in the long list. More accurately, I need to know when it does not exist in the long list.

I thought VLOOKUP might do it but I have not had success.

Reply | Quote

Replies

Let’s say that the long list is in A1:A1000, and that the short list is in D1:D10.
Enter the following formula in E1 (next to the first cell of the short list):

=ISERROR(MATCH(D1,$A$1:$A$1000,0))

Fill down from E1 to E10. The formulas will return TRUE if the value does not occur in the long list, FALSE otherwise. If you’d prefer to display a text only if the value does not occur in the long list, use a variation like this:

=IF(ISERROR(MATCH(D1,$A$1:$A$1000,0)),"Missing","")

Reply | Quote

Hi Don

Does thi do what you want:

=IF(ISNA(MATCH(D2,$B$2:$B$6,0)),”Not in list”,”In list”)

Reply | Quote

Thanks to both of you, they both do just what I needed.

Reply | Quote