Vlookup error

[Nanogirl21]

I am using the below formula to populate a cell based on dates from a different worksheet. Everything is working properly, but I am getting error #N/A when the date is not available. How can I modify the formula to return a blank cell (no text) when an error #N/A is returned?

=IF(ISERROR(VLOOKUP(F10,Dates!$A:$I,2,FALSE))," ",VLOOKUP(F10,Dates!$A:$I,2,FALSE))&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,3,FALSE)&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,7,FALSE)

 

[kweaver]

In your example (in red), you're returning a "space". Have you tried: "" without a space between the quotations?

 

[Marcelo Branco]

<code><code></code></code><code><code></code>Instead of
=IF(ISERROR(VLOOKUP(F10,Dates!$A:$I,2,FALSE))," ",VLOOKUP(F10,Dates!$A:$I,2,FALSE))&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,3,FALSE)&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,7,FALSE)


Try
=IF(ISERROR(VLOOKUP(F10,Dates!$A:$I,2,FALSE)),"",VLOOKUP(F10,Dates!$A:$I,2,FALSE)&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,3,FALSE)&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,7,FALSE))

Hope this helps

M.

</code>

 

[Twollaston]

I am using the below formula to populate a cell based on dates from a different worksheet. Everything is working properly, but I am getting error #N/A when the date is not available. How can I modify the formula to return a blank cell (no text) when an error #N/A is returned?

=IF(ISERROR(VLOOKUP(F10,Dates!$A:$I,2,FALSE))," ",VLOOKUP(F10,Dates!$A:$I,2,FALSE))&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,3,FALSE)&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,7,FALSE)

Not sure if you were meaning the full formula or if you were adding this to the formula already trying to remove the N/A, but to get rid of NA, you can use IFERROR
=IFERROR(IF(ISERROR(VLOOKUP(F10,Dates!$A:$I,2,FALSE))," ",VLOOKUP(F10,Dates!$A:$I,2,FALSE))&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,3,FALSE)&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,7,FALSE),"")

 

[Nanogirl21]

<code><code></code></code><code><code></code>Instead of
=IF(ISERROR(VLOOKUP(F10,Dates!$A:$I,2,FALSE))," ",VLOOKUP(F10,Dates!$A:$I,2,FALSE))&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,3,FALSE)&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,7,FALSE)


Try
=IF(ISERROR(VLOOKUP(F10,Dates!$A:$I,2,FALSE)),"",VLOOKUP(F10,Dates!$A:$I,2,FALSE)&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,3,FALSE)&CHAR(10)&VLOOKUP(F10,Dates!$A:$I,7,FALSE))

Hope this helps

M.

</code>

Perfect. Thank you.

 

[Marcelo Branco]

You're welcome.

M.

 

[Nanogirl21]

I'm trying to use this again an can't get it right. How would I add an ISERROR to the below formula?

IF VLOOKUP(D5,Events!B:Z,9,FALSE) CONTAINS ANY ERROR THEN MAKE THE CELL BLANK

IF VLOOKUP(D5,Events!B:Z,9,FALSE) is not blank then use the formula

=IF(VLOOKUP(D5,Events!B:Z,9,FALSE)="yes",VLOOKUP(D5,Events!$B:$Z,3,FALSE)&CHAR(10)&VLOOKUP(D5,Events!$B:$Z,2,FALSE)&CHAR(10)&VLOOKUP(D5,Events!$B:$Z,6,FALSE),"")

 

[Twollaston]

=iferror(VLOOKUP(D5,Events!B:Z,9,FALSE),””)

 

[Nanogirl21]

=iferror(VLOOKUP(D5,Events!B:Z,9,FALSE),””)

what do i do with the rest of the formula?

• =IF(VLOOKUP(D5,Events!B:Z,9,FALSE)="yes",VLOOKUP(D5,Events!$B:$Z,3,FALSE)&CHAR(10)&VLOOKUP(D5,Events!$B:$Z,2,FALSE)&CHAR(10)&VLOOKUP(D5,Events!$B:$Z,6,FALSE),"")
  ​

 

[Marcelo Branco]

Try something like this

=IF(ISNUMBER(MATCH(D5,Events!B:B,0)),"rest of the formula","")

M.