Excel - =IF(ISERROR(VLOOKUP(A3,contacts!B:I,5,FALSE)), " ", VLOOKUP(A3,con

Asked By Jim on 02-Feb-10 11:08 AM
Hello,

I am using this vlookup, however it is returning a blank if the cell it is
referencing is blank.  how do I write this formula so the return of a blank
cell is blank?

=IF(ISERROR(VLOOKUP(A3,contacts!B:I,5,FALSE)), " ",
VLOOKUP(A3,contacts!B:I,5,FALSE))

Thanks




Fred Smith replied to Jim on 02-Feb-10 11:29 AM
You lost us. You just told us the formula is doing exactly what you want
("return of a blank cell is blank"). You'll need to provide a clearer
explanation of your problem. Examples are normally the best way.

Regards,
Fred
Dave Peterson replied to Jim on 02-Feb-10 11:34 AM
Maybe...

Use "" instead of " "

=if(iserror(vlookup(...)),"",vlookup(...))



--

Dave Peterson
Ms-Exl-Learner replied to Jim on 02-Feb-10 11:41 AM
=IF(ISERROR(VLOOKUP(A3,contacts!B:I,5,FALSE)), " ",
VLOOKUP(A3,contacts!B:I,5,FALSE))

Vlookup never result blank when the lookup value is present in the lookup
range.  It will result 0 value when the resulting column is not having any
value.

I think you are looking for a formula like the below:-

=IF(ISNA(VLOOKUP(A3,Contacts!B:I,5,FALSE)),"Lookup Value is not Available in
The Lookup Range",VLOOKUP(A3,Contacts!B:I,5,FALSE))

If you want to show the Display message as BLANK when the lookup value is
not present in Lookup range then use the below one:-

=IF(ISNA(VLOOKUP(A3,Contacts!B:I,5,FALSE)),"BLANK",VLOOKUP(A3,Contacts!B:I,5,FALSE))

Instead of using ISERROR use ISNA.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------
Jim Thomlinson replied to Jim on 02-Feb-10 11:44 AM
Here is the answer I posted on Jan 26 when you asked almost the exact same
question...

=IF(or(ISNA(VLOOKUP(F2,Content_Insert!A:A,1,0)),
VLOOKUP(F2,Master!A:D,2,0)=""),"",VLOOKUP(F2,Master!A:D,2,0))

so in this case...

=IF(or(ISna(VLOOKUP(A3,contacts!B:I,5,FALSE)),
VLOOKUP(A3,contacts!B:I,5,FALSE) = ""), "",
VLOOKUP(A3,contacts!B:I,5,FALSE))
--
HTH...

Jim Thomlinson