Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Data Validation drop down list

Copper Contributor

Can I create a list of codes so that when you choose the code from the drop-down list, you get a definition of the code?

For example:

Code                          Definition

AAT                            Arranging for assistive technology


What steps do I need to take?

Thank you.

1 Reply


Hi agkrolak,

First you will need to create a list of all your Code-Definition pairs. This can be on a separate sheet.
Next I would declare each column of that list as a Named Range (call one Codes and the other Definitions).

  • If the list will never change length, you can do this just by selecting the whole range in the column (not the whole column - just the cells that contain data) and typing the new name into the Name Box (I'm assuming you know where that is - if not let me know).
  • If the list will change length, you will need to declare dynamic Named Ranges, using either OFFSET() or INDEX() - here's a great tutorial:

Now, for your Code drop-down cell, go to Data/Data Validation, and in the Allow drop-down, select List, then in Source put =Codes.

For the Definiton cell, use a lookup function like XLOOKUP(), or INDEX(MATCH()). The function call will be something like this (if your Code drop-down is cell A1).

=XLOOKUP(A1,Codes,Definitions,"Code not found",0)

 The "Code not found" error should actually be completely redundant, because your dropdown list is defined on the same data as the lookup, so in theory it can't fail! But I always like to put in a fail message... force of habit!

Hope that works for you!