This value doesn't match the data validation restrictions defined for this cell when using Vlookup from a list

Question

Hello,

I am trying to use VLOOKUP to find data from another sheet when I select data from a list. When I enter the formula it shows up correctly but when I press enter I get this message "This value doesn't match the data validation restrictions defined for this cell" Can you please tell me what I'm doing wrong?

=VLOOKUP(A2,'Data Validation'!A2:B4,2,false)

I have another sheet that has all my data on it, on my main sheet i made a list using data validation.

Answer 1

The message means you have a data validation in the cell, that has nothing to do with the part 'Data Validation'!A2:B4 in your formula. Please read this article what a data validation means: https://support.microsoft.com/en-us/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249

If you have a formula in a cell it makes no sense to apply a data validation to the cell, because a data validation check manual input only. You can not restrict a cell if it contains a formula.

And if you already have a data validation in the cell and you want to into a formula you'll get the message because your formula itself (the formula text!) did not match the restrictions of the data validation. The formula might return a result that match the data validation, but a formula is calculated after the input, not during... hence the error.

Andreas.