Use data in SharePoint List for dynamic formula creation

[craigja]

I have a mapping 'table' in a SharePoint List, I have an External code and Internal code column and I need to use the data in these fields to build up a formula using the Contains function, something like below:

if contains([Details 2], 'Craig') then 'A123' elseif
contains([Details 2], 'James') then 'A456' elseif
contains([Details 2], 'Bob') then 'A789' elseif

else '' endif

So in the above example, for row 1 - 'Craig' would be External Code and 'A123'  would be the Internal code

I'm stumped with how to get this into my formula tool, I dont know how many entries on this list there will be as there is a team that will be managing this mapping List

[alexnajm]

I would create the mapping then, and use a find Replace to map them in! Instead of replacing, you can append the column: Tool Mastery | Find Replace rechercher et remplacer suchen/ersetzen

[craigja]

Thats sort of what we did in the end - we appended all the lookups to each row of data, creating thousands of rows, then we could add a simple formula that updated a match column to true or false

[alexnajm]

That works too!