Not Posting a Value to a Validation Cell

Welcome to the Forum Nutrastat!

I am having trouble following your description. Not sure what you mean when you use the word "post" because you seem to be using it to mean more than one thing.

I can't tell if the crux of the problem is the data validation will not accept null, or a problem in the VLOOKUP, or a problem in an IF formula.

This is where the validation cell will not accept a null string (i.e. "").

You can modify your validation rule to expliclitly accept the null string. In fact, the cell should be blank before the user selects anything 1...100.

VLookup formula

Please show the formula

the IF formula

Please show the formula

BTW consider using XLOOKUP instead of VLOOKUP.

It sounds like you have a large file but this will go a lot faster if you can attach your file for analysis.

The reason I found your initial description confusing is that you did not explicitly say that you have a formula and also a data validation dropdown list in the same cell. This is not unheard of but it's unusual and I'm wondering why you are doing it. Is there something you are not showing in this simplified example that allows the user to override the formula for some reason?

The problem is that if you refer to a blank cell, it is converted to zero by default. There are two possibilities I can think of:

Modify your formula to explicitly return a blank if it finds a blank. This is probably the safest and most straightforward. I have attached your sample file with this solution.

Formula:

=IF(Select_Complete="No","",LET(N,VLOOKUP(A16,Array_Data,4,FALSE),IF(N="","",N)))

Modify your formula to force results to be converted to text

Formula:

=IF(Select_Complete="No","",VLOOKUP(A16,Array_Data,4,FALSE)&"")

This might be an issue depending on what else you are doing with that result.

Go to Options > Advanced > Display options for this worksheet
and uncheck "Show a zero in cells that have zero values"
However, the cell will still have a zero value, will turn red from your CF, and may have other downstream side effects that you don't want (note my first paragraph). This is my least favorite.