VLookup Error

See Cheeky Charlie example

or using your data

VBA Noob

I recommend CC's VLOOKUP. It's easy to maintain, and the table can be put in an out-of-the-way place -- chances are, with some other lookup tables.

There are usually several ways to do something in Excel.

=LOOKUP(A35, {"Belfast","Durham","Glasgow","GRO","ION","Liverpool","London HQ","London Regional","Newport","Peterborough"},
{"2064203","2074203","2054203","Nil","5004209","2024203","5004203","5004211","2044203","2034203"})

A35 is matched in the first list, and the value in the corresponding position in the second list is returned. The values in the first list must be in ascending order.

Here's a way to embed the lookup table in the formula. It's exactly equivalent to CC's suggestion, but harder to maintain. The False argument requires an exact match, allowing the items to be in any order:

=VLOOKUP(A35, {"Belfast",2064203;"Durham",2074203;"Glasgow",2054203;"GRO","Nil";"ION",5004209;
"Liverpool",2024203;"London HQ",5004203;"London Regional",5004211;"Newport",2044203;"Peterborough",2034203}, 2, FALSE)

The commas in the list are column breaks, and the semicolons are row breaks.