Way to Use Lookup formula in Data Validation

Original Question:-

How to use Lookup formula in Data Validation?


What i want to do sounds simple but I haven't found a solution.
If name/type is "cable" I want a dropdown list for "att" allowing only code1 values.
If name/type is "htrc" I want a dropdown list for "att" allowing only code2 values.
What I've tried is:
- defining a named range called "codes" for $A:$A on sheet1
- data validation for B2 on sheet2 and used formula: =IF($A2="cable";INDIRECT(codes="code1");INDIRECT(codes="code2"))
When I'm entering that I'm getting a message saying "The Source currently evaluates to an error. Do you want to continue?" and the dropdown is blank...

Sheet 1:-

image 1


Sheet 2:-


image 2


We create the drop down list and we use the “OFFSET”, “MATCH”, “VLOOKUP” and “COUNTIF” in data validation.

  • Follow below given steps:-
  • Select the cell B2 in sheet 2.
  • Go to Data tab > Data Validation > Settings > List.
  • Enter the formula:- =OFFSET(codes, MATCH(VLOOKUP(G6, Keynames, 2, 0), codes, 0)-1, 1, COUNTIF(codes, VLOOKUP(G6, Keynames, 2, 0)), 1)
  • Click on ok.
  • Copy the Data validation in paste in the range.

image 3


Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube