Vlookup

[Steved]

Hello from Steved

=IF(ISNA(VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6)),"",VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6))

I have 2 issues please A col is the where 92210 is

E6 is a number ie "92210 in Audit Team" which is actually
Sheet2 but named Audit Team in this Case. 6 Cols is a
Surname. If I type in 92210 it gives me Smith which is
what it should do, but if I type in 92211, there is no
such number hence it keeps in Smith where the cell should
be blank in this case, How can I correct this please.
Also I am posted a new list every week to put in Audit Team
but when I input it and put in 92210 the name Jones comes
up, so I tried to copy it then use paste special values I
still got the same result. Help.

Thankyou.

 

[JE McGimpsey]

Steved,

You need to include the 4th argument to VLOOKUP, else it will return an
approximate match, not #N/A.


=IF(ISNA(VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,0)),"",VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,0))

 

[CLR]

Try adding the FALSE option to the end of your VLOOKUP formulas...........

=IF(ISNA(VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,FALSE)),"",VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,FALSE))

Vaya con Dios,
Chuck, CABGx3

 

[Steved]

You are a Gentleman Thankyou.

-----Original Message-----
Steved,

You need to include the 4th argument to VLOOKUP, else it will return an
approximate match, not #N/A.


=IF(ISNA(VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,0)),"",VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,0))




.

 

[Guest]

Steved,
Youre asking it to find the closest match by omitting the 4th argument.
=IF(ISNA(VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,FALSE)),"",VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,FALSE))

or instead of "FALSE", use 0 (zero)

Harry

 

[Steved]

Thanks Harry

-----Original Message-----
Steved,
Youre asking it to find the closest match by omitting the 4th argument.
=IF(ISNA(VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,FALSE)),"",VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,FALSE))

or instead of "FALSE", use 0 (zero)

Harry




.

 

[Steved]

Thanks Chuck.

-----Original Message-----
Try adding the FALSE option to the end of your VLOOKUP formulas...........

=IF(ISNA(VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,FALSE)),"",VLOOKUP(E6,'Audit Team'!
$A$2:$D$2000,6,FALSE))

Vaya con Dios,
Chuck, CABGx3






.