How do i stop my formula displaying #N/A 1

[Westicle]

I want my formula to do the following:
lookup a value (staff number) on one sheet that contains all staff numbers and compare to another that only has some. If the number exists on both then return a value (costs) from the second sheet. If it does not exist on both then i want it to display nothing (not #N/A)

This displays #N/A
=IF(VLOOKUP(A1,SMP,1, FALSE) = A1, VLOOKUP(A1,SMP,3,TRUE), "&quot

As does this
=IF((ERROR.TYPE(IF((VLOOKUP(A22, SMP!$A$1:$C$18, 1) = A22), "Return Costs", "&quot)) = 7, "", "Return Costs&quot

and now my brain hurts. HELP!!

 

[xlbo]

You got your order of logic the wrong way round - IF(logic test,true,false) is the standard way round so if you are searching for an error, the TRUE statement refers to the finding of an error and the FALSE should be your lookup:

=IF(ISNA(VLOOKUP(A1,SMP,1, FALSE)),"", VLOOKUP(A1,SMP,3,TRUE))

HTH
Rgds
~Geoff~

 

[rmikesmith]

westicle,

Qualify your second A1 reference with the appropriate sheet name. Ex:

=IF(VLOOKUP(A1,SMP,1, FALSE) = Sheet2!A1, VLOOKUP(A1,SMP,3,TRUE), "&quot


This worked for me.

Regards,
M. Smith

 

[SQLScholar]

hey,

what is this ISNA??

I have never seen this before but it may be handy for my future work if it does what i think it does!!

regards

Dan

 

[Westicle]

Cheers xlbo that works fine. i was sure there was a way to do but i was missing the vital ingrediant.

 

[xlbo]

ISNA is a check for the #N/A error - typically given if a vlookup can't find something it's looking for. You could also use ISERROR but this will not distinguish other errors such as #REF! and DIV0 Rgds
~Geoff~