Formula to stop returning a date of 00-Jan-00

[Peggy2015]

Hello,

I have the following formula in my spread sheet: =IFERROR(VLOOKUP(A5,'Staff Data'!A:K,8,FALSE),""), however the results it brings back is: 00-Jan-00. How do I write a better, more robust, formula that ignores blank cells so it doesn't give me 00-Jan-00?

thanks for your help.

Best wishes,
Peggy

 

[poulmax]

Hi
You could nest in a IF-sentence more:
=IF(VLOOKUP(A5,'Staff Data'!A:K,8,FALSE)<>0;IFERROR(VLOOKUP(A5,'Staff Data'!A:K,8,FALSE),"");"")

 

[Peggy2015]

Hello,

Thanks for the formula. Unfortunately, I now get #NA when the formula is looking up my criteria in cell A5, for example, and the information does not exist in 'Staff Data'! at all.

I also tried Index,Match, but it didn't like that either - same reply #NA when the data does not exist in the spreadsheet it is looking up the information. How can I work around this? I thought about putting in a nested formula for IFNA, but that didn't work for me. I might have been writing the formula incorrectly?

Thanks,
Peggy

Hi
You could nest in a IF-sentence more:
=IF(VLOOKUP(A5,'Staff Data'!A:K,8,FALSE)<>0;IFERROR(VLOOKUP(A5,'Staff Data'!A:K,8,FALSE),"");"")

 

[poulmax]

Hi
My mistake - this should work:

=IFERROR(IF((VLOOKUP(A5;'Staff Data'!A:K;8;FALSE))<>0;VLOOKUP(A5;''Staff Data''!A:K;8;FALSE);"");"")

 

[Peggy2015]

Hello,

Thank you very much for your help. This works like a charm!

Regards,
Peggy

Hi
My mistake - this should work:

=IFERROR(IF((VLOOKUP(A5;'Staff Data'!A:K;8;FALSE))<>0;VLOOKUP(A5;''Staff Data''!A:K;8;FALSE);"");"")