CODEWITHSUNDEEP
excelformula
WixLove
WixLove

Reputation: 73

Excel formula Iferror

I have a drop down list (data validation) that populates other cells using vlookup that pulls data from a separate worksheet. I'd like to include an error message if the field is blank like "No data entered". However, I am getting "0" instead. Here is the formula I used:

=IFERROR(VLOOKUP(ChildName,Children,6,FALSE),"No data entered")

I then changed this to an ISERROR:

=IF(ISERROR(VLOOKUP(ChildName,Children,5,FALSE))=FALSE,"No data entered", VLOOKUP(ChildName,Children,5,FALSE))

This gives the error message appropriately but now my drop down list does not populate the other cells! What am I doing wrong? Thank you!

Upvotes: 0

Views: 2204

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

Looking at your formulas, I see two noteworthy points:

  1. in the IFERROR you return the 6th column, in the ISERROR, the 5th!
  2. in the ISERROR, you show the error message when something is found, as you say =IF(ISERROR=FALSE,ErrorMsg,...)! To solve your problem, simply use this formula: 
    =IF(ChildName="","No data    entered",IFERROR(VLOOKUP(ChildName,Children,6,0),"Cannot find    "&ChildName))

Ah, I see! Check out this question - Have Excel formulas that return 0, make the result blank. It shows all ways.

My favorite is =IFERROR(1/1/VLOOKUP(ChildName,Children,6,0),"No data!"). Only works with numbers though.

Else, use =IF(VLOOKUP(ChildName,Children,6,0),VLOOKUP(ChildName,Children,6,0),"No data!").

To be on the safe side, this version should take care of all potential issues: =IFERROR(IF(VLOOKUP(ChildName,Children,6,0),VLOOKUP(ChildName,Children,6,0),"No data!"),"No data!")

Upvotes: 2

Related Questions