I want to restrict a value in a cell based on another cell in the same row.

The value in A must not exceed what is in B. If the value in B is zero, any figure is allowed in A

A | B
4 | 9     – allowed

5 | 0     – allowed

6 | 5     – the value in A should not be allowed

How to set up data validation for this in column A.

I want all rows in Column A to have this validation rule.

  • I tried data validation -> formula =a1 <= B1 --- nor worked - blocking any change to cell (more or less than B1) Jun 28, 2019 at 11:39
  • The validation formula =a1<=b1 is working in a blank sheet. But, I have some vlookup formula in in a1 and b1. When I want to change the value in A1 by overwriting the vlookup function, I want the entered value must not exceed B1 (which is result of another vlookup function) Jun 28, 2019 at 12:11

2 Answers 2


Set the data validation for A1 as follow:

  • On Cell range set the cell range from Column A.
  • On the Criteria dropdown select: Custom formula is
  • On the formula input box add this formula: =OR(B1=0,A1<=B1)
  • On the On invalid data radio buttons selet Reject input

Dont' forget to click Save


If values in A1 & B1 cells output values as a result of formulae this won't be possible to "restrict" via Data Validation. In such cases, the common practice is to wrap your formulae in IF statement and direct it towards outputting nothing or error if the condition is not met. A1 then be like:

=IF(B1=0, your_A1_vlookup_formula_here, 
 IF(your_A1_vlookup_formula_here>B1, , your_A1_vlookup_formula_here))

  • The formula is getting the value all right. But, what I want is data validation. The user must not be allowed to increase the value of A1 (by typing in a value) which more than B1 (the Bi is hidden from the user). A1 and B1 are both having a vlookup formula to start with. The user can type in any value only if B1 is evaluated to zero Jul 1, 2019 at 10:13
  • like already mentioned, you cant use data validation on outputs from vlookup
    – user0
    Jul 1, 2019 at 10:15
  • 1
    It's working as desired by the formula in data validation =or(S2128=0,Q2128 <= S2128) Jul 1, 2019 at 10:27
  • The above formula is not always working. I modified the validation formula slightly (for the range Q2:Q =or((S2:S)=0,((Q2:Q)-(S2:S))<=0 ) ........ It is blocking values higher than contained in S, but allow any value if S=0 (in same row) Jul 2, 2019 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.