VLOOKUP with empty table

[R0CKY]

hi all

if used the formula VLOOKUP with empty table will give 1900-01-00 how keep empty table empty

=IFERROR(VLOOKUP(B18,IF({1,0},Sheet4!$B$4:$B$502,Sheet4!$I$4:$I$502),2,0),"")

I'd be really thankful if someone has a solution for this.

[AliGW]

As there is no second column in either of the arrays you have entered into the VLOOKUP formula, this cannot be working.

[Arkadi]

Assuming the iferror is to avoid other issues, you could try checking if the current formula results in 0 (which in a date field is 1900-01-00?)

Try:

Please Login or Register  to view this content.

I also don't see the lookup array having 2 columns?

[Jonmo1]

@AliGW & Arkadi
It's actually a neat little trick, and it does work.
It the IF {1,0} bit creates a 2 column array with B and I
Although I've previousely seen it with CHOOSE instead of IF..

@Rocky
The issue is that 1900-01-00 is really just a 0 with a Date Cell formatting.
And 0 is NOT an error, therefor it will not be trapped by Iferror.

You would have to do
=IF(VLOOKUP(...)=0,"",VLOOKUP(...))

[Jonmo1]

Since the cell containing the formula is formatted as a date (0 shows as 1900-01-00)
That suggests you're expecting the resulting value of the vlookup to be a Date.

Then try
=IFERROR(1/(1/VLOOKUP(B18,IF({1,0},Sheet4!$B$4:$B$502,Sheet4!$I$4:$I$502),2,0)),"")

[R0CKY]

thanks Arkadi This works perfectly, thank you so so much!

[R0CKY]

thanks Jonmo1 also it's work thank you so much!

[Arkadi]

R0CKY, you are welcome, glad we could help
Please remember to mark the thread as solved if you are happy with the solutions provided? Thanks!