[mso] Re: Check for existence in column? Excel 2000

Hi Robert,
try this formula:
=IF(VLOOKUP(B3,LastNames,1,FALSE)=B3,VLOOKUP(B3,LastNames,1,FALSE),NA())
where LastNames is the range name for your column, or substitute by the 
column address (for example, $F1$:$F100 ). Put it anywhere except on B3 or 
on the data range you're searching.
If "Smith" is on the range, and you have SMITH on B3, the formula will 
return SMITH. If it's not on the range, it will return the #N/A error value.
Wilson

At 15:52 20/12/2004, Green wrote:
>Hi Robert,
>
>I believe a function like "=If_Exists(B3, LastNames, B3,"")"  will
>give you a circular reference error.
>Also... even if it did work you would get lots of gaps in the column
>the function is in. Is that what you want?
>
>I can think of 3 "other" ways to confirm wether something is in a
>column.
>
>1) Use CountIf.. Ray is King on that and I'm sure he can explain it to
>you.
>2) Use VBA to prompt you for a name and then tell you if it exists.
>3) Use a filter.
>
>Lisa
>
> > I am looking for a variation of vlookup, please. If I have
> > a column called
> > LastNames, and some names below it such as:
> >
> > LastNames
> > Apple
> > Banana
> > Conkright
> > Davidson
> >
> > How do I check that simply for the existence of a name? If
> > I want to see if
> > "Smith" is in that list, everything I check so far seems to
> > want me to refer
> > to the column next to it. I.e.,
> > =vlookup("Smith",LastNames, 0)
> > will not work.
> >
> > =vlookup("Apple",:LastNames,1)
> > returns whatever is in the cell next to it, which is not
> > what will work
> > here.
> >
> > Does anyone know how take a cell reference, check for its
> > existence, return
> > the name if exists, return nothing if not?
> > Made up example:
> >
> > =If_Exists(B3, LastNames, B3,"")
> > would be lovely if there was such a function.
> > If B3 is "Apple", then it will return "Apple" since it exists.
> > If B3 = "Smith", then it returns a blank since it does not exist.
> >
> > Am I making sense?
> >
> > Thank you.
> >
> > Robert
>
>
>*************************************************************
>You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
>MicrosoftOffice@xxxxxxxxxxxxxxxx
>
>To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
>To Unsubscribe from this group, send an email to
>mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) 
>in the subject line.
>
>Or, visit the group's homepage and use the dropdown menu.  This will also 
>allow you to change your email settings to digest or vacation (no mail).
>http://www.freelists.org/webpage/mso
>
>To be able to use the files section for sharing files with the group, send 
>a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an 
>invitation with instructions.  Once you are a member of the files group, 
>you can go here to upload/download files:
>http://www.smartgroups.com/vault/msofiles
>*************************************************************


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: