It may be the OP is checking for specific PK numbers of which 55 is not one of them.
UPDATE: Ah! Just seen the word "could" in your replyTo be fair, I did kind of make the assumption that these were known, current, specific PK codes and, consequently, the formula could grow if more codes were added. On that basis, it wouldn't really be scaleable. I would be inclined to list all valid PK codes in a separate configuration sheet and define a Dynamic Named Range to facilitate lookup. In that way, the valid/invalid PK codes could be checked and, for the valid ones, you can then use the shorter formula.the OP i think needs specific number of characters based on PK##(len 4) or PK#(len 3)
Essentially, it is duplicating the formula, hence the desire to shorten it. The first version will check for PK codes with an alpahnumeric "prefix". If it doesn't find anything, it will try again by adding a zero to the numeric "prefix", this converting it from a text value (from the LEFT function) to a numeric value. In my simple testing, that works. In other, more complex scenarios, it may not.and try to work out what the formula is actually doing.
An alternative approach would be to convert the lookup table, column A, to Text (rather than a mix of alphanumeric and pure numeric values).
Bookmarks