I'm trying to get a Vlookup to look at certain parts of a Wooksheet and tell
me if a person is in it or not and return a specific value if they are or
arent. I can get it to return the value for if they are, but if they arent
it comes back with #N/A

Can anyone please help this is doing my head in!!!

Cheers

The usual way of doing this is along the lines of:

=IF(ISNA(your_lookup_formula),"",your_lookup_formu la)

i.e. if there is an error in the lookup then return an empty string
(blank), otherwise do the lookup. You could change the "" to some error
message like "absent".

Hope this helps.

Pete

Balticjo wrote:

I'm trying to get a Vlookup to look at certain parts of a Wooksheet and tell
me if a person is in it or not and return a specific value if they are or
arent. I can get it to return the value for if they are, but if they arent
it comes back with #N/A

Can anyone please help this is doing my head in!!!

Cheers

Sorry should have said this is what i'm using at the moment

=IF(VLOOKUP(A159,'1st'!$I:$I,1,FALSE)=A159,"W","A" )

"Balticjo" wrote:

I'm trying to get a Vlookup to look at certain parts of a Wooksheet and tell
me if a person is in it or not and return a specific value if they are or
arent. I can get it to return the value for if they are, but if they arent
it comes back with #N/A

Can anyone please help this is doing my head in!!!

Cheers

Ok, try this variation of it:

=IF(ISNA(VLOOKUP(A159,'1st'!$I:$I,1,0)),"not
present",IF(VLOOKUP(A159,'1st'!$I:$I,1,0)=A159,"W" ,"A"))

Hope this helps.

Pete

Balticjo wrote:

Sorry should have said this is what i'm using at the moment

=IF(VLOOKUP(A159,'1st'!$I:$I,1,FALSE)=A159,"W","A" )

"Balticjo" wrote:

I'm trying to get a Vlookup to look at certain parts of a Wooksheet and tell
me if a person is in it or not and return a specific value if they are or
arent. I can get it to return the value for if they are, but if they arent
it comes back with #N/A

Can anyone please help this is doing my head in!!!

Cheers

Sorry, I think what you want is:

=IF(ISNA(VLOOKUP(A159,'1st'!$I:$I,1,0)),"A","W")

i.e. if A159 is not in the table return "A", if it is in the table then
return "W"

Hope this helps.

Pete


Pete_UK wrote:

Ok, try this variation of it:

=IF(ISNA(VLOOKUP(A159,'1st'!$I:$I,1,0)),"not
present",IF(VLOOKUP(A159,'1st'!$I:$I,1,0)=A159,"W" ,"A"))

Hope this helps.

Pete

Balticjo wrote:

Sorry should have said this is what i'm using at the moment

=IF(VLOOKUP(A159,'1st'!$I:$I,1,FALSE)=A159,"W","A" )

"Balticjo" wrote:

I'm trying to get a Vlookup to look at certain parts of a Wooksheet and tell
me if a person is in it or not and return a specific value if they are or
arent. I can get it to return the value for if they are, but if they arent
it comes back with #N/A

Can anyone please help this is doing my head in!!!

Cheers

Worked a Treat

Much Appreciated!!!

"Pete_UK" wrote:

Ok, try this variation of it:

=IF(ISNA(VLOOKUP(A159,'1st'!$I:$I,1,0)),"not
present",IF(VLOOKUP(A159,'1st'!$I:$I,1,0)=A159,"W" ,"A"))

Hope this helps.

Pete

Balticjo wrote:

Sorry should have said this is what i'm using at the moment

=IF(VLOOKUP(A159,'1st'!$I:$I,1,FALSE)=A159,"W","A" )

"Balticjo" wrote:

I'm trying to get a Vlookup to look at certain parts of a Wooksheet and tell
me if a person is in it or not and return a specific value if they are or
arent. I can get it to return the value for if they are, but if they arent
it comes back with #N/A

Can anyone please help this is doing my head in!!!

Cheers

Thanks for the feedback.

Pete

Balticjo wrote:

Worked a Treat

Much Appreciated!!!

"Pete_UK" wrote:

Ok, try this variation of it:

=IF(ISNA(VLOOKUP(A159,'1st'!$I:$I,1,0)),"not
present",IF(VLOOKUP(A159,'1st'!$I:$I,1,0)=A159,"W" ,"A"))

Hope this helps.

Pete

Balticjo wrote:

Sorry should have said this is what i'm using at the moment

=IF(VLOOKUP(A159,'1st'!$I:$I,1,FALSE)=A159,"W","A" )

"Balticjo" wrote:

I'm trying to get a Vlookup to look at certain parts of a Wooksheet and tell
me if a person is in it or not and return a specific value if they are or
arent. I can get it to return the value for if they are, but if they arent
it comes back with #N/A

Can anyone please help this is doing my head in!!!

Cheers

If you're not returning a value but just checking whether an item exists in a
list then MATCH would be better:

=IF(ISNA(MATCH(A159,'1st'!$I:$I,0)),"A","W")

"Balticjo" wrote:

Sorry should have said this is what i'm using at the moment

=IF(VLOOKUP(A159,'1st'!$I:$I,1,FALSE)=A159,"W","A" )

"Balticjo" wrote:

I'm trying to get a Vlookup to look at certain parts of a Wooksheet and tell
me if a person is in it or not and return a specific value if they are or
arent. I can get it to return the value for if they are, but if they arent
it comes back with #N/A

Can anyone please help this is doing my head in!!!

Cheers