Solved How to run 2 x vlookup and not return 0 or 00/01/1900

October 2, 2020 at 06:27:53
Specs: Windows 10
Hi All..

I have a formula which is unfortunately pulling in cells where no data exists and returning 0 and when a date is zero I am getting the 00/01/1900. How can I modify it to NOT enter blank dates or zero's pls.

Any help would be awesome..

Formula is

IF($G3246="GB03",VLOOKUP($N3246,'GB03'!$A$2:$G$99999,4,FALSE),
IF($G3246="GB30",VLOOKUP($N3246,'GB30'!$A$2:$K$99999,3,FALSE),""))))))


See More: How to run 2 x vlookup and not return 0 or 00/01/1900


#1
October 2, 2020 at 07:05:24
Your formula is not valid. Why do you have so many parenthesis at the end?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 2, 2020 at 07:37:08
✔ Best Answer
2 options come to mind:

1 - Hardcode a zero test into your formula:

=IF($G3246="GB03",IF(VLOOKUP($N3246,'GB03'!$A$2:$G$99999,4,0)=0,"",
VLOOKUP($N3246,'GB03'!$A$2:$G$99999,4,0)),IF($G3246="GB30",
IF(VLOOKUP($N3246,'GB30'!$A$2:$K$99999,3,0)=0,"",
VLOOKUP($N3246,'GB30'!$A$2:$K$99999,3,0)),""))

2 - Set your spreadsheet options to not display 0 values. This will hide all zero values, so this option may have undesirable impacts on your workbook.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question