Let A1:A4 contain...

P***N
P1***
P2***
C***4

....and B1:B4 contain the corresponding 'Cost Code', then try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER...

=INDEX(B1:B4,MATCH(TRUE,ISNUMBER(SEARCH(A1:A4,D1)) ,0))

....where D1 contains your lookup value, such as P1234.

Hope this helps!

In article ,
Jonathan May
wrote:

I have a problem where I'm looking for a general approach rather than
specific answers...

..I have a string such as P1234 and a series of rules that assign cost
codes to this string. Such as:
P***N Cost Code 1
P1*** Cost Code 2
P2*** Cost Code 3
C***4 Cost Code 4, etc

In the above example the first rule would trump the subsequent rules.

I would love VLOOKUP to have a wildcard function but I don't think
there is one. My current solution has a bunch of nested IFs and
Lookups but its non-flexible and has to be rebuilt every time cost code
structures change.

Any thoughts on an approach to this?

Thanks in Advance!