Data Validation drop down list

agkrolak 

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: https://exceljet.net/glossary/dynamic-named-range

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!

Colin