Help with IFERROR and VLOOK up returning zero instead of blanks

[Meady]

Hi Chaps, I have two spread sheets which I need to which I need to cross check information. The formula works except I get a zero where I want a blank cell and a black cell if the entry doesn't exist on both sheets, this is making me loose my mind.

=IFERROR(VLOOKUP(A5,'FRA MASTER LIST - FRAs due Aug_Sep.xlsx'!Table1[[#Headers],[#Data]],26,FALSE),"")

the only time this works is if the property is on one sheet and not the other.

I have the same properties in both books, and I need to transfer dates in one column in one sheet to the other, I would do it manually but there are nearly 500 entries....

Any help would be much appreciated!

 

[etaf]

so you will get a blank if there is an error - ie the value can not be matched
But if it can be matched and the cell to return on the other sheet is a blank cell - vlookup will read that as a zero, as its NOT an error

What do you want to happen if there is a Match but column 26 is blank ?
what do you want to happen if the lookup cannot be found

 

[Meady]

Hi,

That is my problem, that zero is not an error, I didn't realise that . Essentially, I don't want to show the zero's. Therefore, if there is no value (in this case a date) entered in column 26, I want the vlookup to ignore it.

I did have the text "No Information" between the quotation marks at the end which worked perfectly if the lookup could not be found, i.e. data entry on one sheet and not on the other.

 

[BQardi]

You could do this:

=IFERROR(SUBSTITUTE(VLOOKUP(A5,'FRA MASTER LIST - FRAs due Aug_Sep.xlsx'!Table1[[#Headers],[#Data]],26,FALSE),"",""),"")

 

[Meady]

You could do this:

=IFERROR(SUBSTITUTE(VLOOKUP(A5,'FRA MASTER LIST - FRAs due Aug_Sep.xlsx'!Table1[[#Headers],[#Data]],26,FALSE),"",""),"")

Thanks for you help, that does work quite nicely. There is one issue though, using the above converts my dates to numbers and not even formatting the cells will change it back.

 

[BQardi]

I was trying not to lookup the value twice, but if nothing else this will always work:

=IFERROR(IF(VLOOKUP(A5,'FRA MASTER LIST - FRAs due Aug_Sep.xlsx'!Table1[[#Headers],[#Data]],26,FALSE)=0,"",VLOOKUP(A5,'FRA MASTER LIST - FRAs due Aug_Sep.xlsx'!Table1[[#Headers],[#Data]],26,FALSE))