This drove me mad for about 10 minutes …

Excel has a nice function called Lookup which allows you to set a cell value, based on another cell value, which appears in another list.

Having written the function, I found it was persistently returning incorrect values.

I had missed this little nugget from Microsoft’s own online help;

Important   The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

So oddly, for Lookup to work, the lookup list has to be sorted in alphabetical order – wonder who programmed that one!