Most efficient method to lookup and change data

[jimrward]

I have a large table of which one of the columns is customername over the years due to name changes, bad typing etc some of the names have become garbled
Currently I have a small lookup table with garbledname, preferredname which I buffer
I then create a custom column namechange which gets to either -1 no match or position in buffer
I then add another custom column pname which gets set to customername or the preferredname
I know there is an extra step and I could have combined them to say
If list.positionof…. =-1 then
Either way it requires a lookup and effectively an index
As a double lookup has always been bad practice in excel is there a better more efficient method

 

[AlienSx]

make a record out of your lookup table using Record.FromList(lookup_tbl[preferredname], lookup_tbl[garbledname]). Then use Record.FieldOrDefault to get preferredname from this record.