Vlookup

  • Thread starter Thread starter Steved
  • Start date Start date
S

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.
 
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))
 
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
 
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))




.
Click to expand...
 
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
 
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




.
Click to expand...
 
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






.
Click to expand...
 
Back
Top