Wildcard vlookup perhaps?

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!

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 <[email protected]>,
Jonathan May
<[email protected]> 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!

It does indeed help.

Your are truely an Excel God!

Many thanks

Actually, for the values contained in A1:A4, the following would
suffice...

P*N
P1*
P2*
C*4

Also, if you want the formula to be case-sensitive, use FIND instead of
SEARCH.

Hope this helps!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> 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 <[email protected]>,
> Jonathan May
> <[email protected]> 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!

Humm, interesting - I have other strings of 3 and 4 characters but I'm using Len() to look at the appropriate list.

Is there any way of doing the "indexed matched search" so that you could ensure you're only looking at, say, characters 1, 3, and 6, e.g. so that P*2**5, would give a distinct value from P**2*5?

Luckly only the left and right ends of my string change so (at the moment) this case doesn't apply and I'm not desperate for an answer

Thanks again!

Johnathan,

I was just going to post again to correct myself and address this issue.
Enter the values for A1:A4 as follows...

P???N
P1???
P2???
C???4

And for your other examples, the values would be entered as follows...

P?2??5
P??2?5

....and I would change the formula to the following...

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

....confirmed with CONTROL+SHIFT+ENTER. Otherwise, the previous formula
would return a match even if there were other characters before or after
your text value.

Hope this helps!

In article <[email protected]>,
Jonathan May
<[email protected]> wrote:

> Humm, interesting - I have other strings of 3 and 4 characters but I'm
> using Len() to look at the appropriate list.
>
> Is there any way of doing the "indexed matched search" so that you
> could ensure you're only looking at, say, characters 1, 3, and 6, e.g.
> so that P*2**5, would give a distinct value from P**2*5?
>
> Luckly only the left and right ends of my string change so (at the
> moment) this case doesn't apply and I'm not desperate for an answer
>
>
> Thanks again!

That really is elegant - I can think of dozens of uses for this - you're going to save me hours, if not days, of work!

I think I'd better study 'Index' and 'Match' a little more...

Many thanks,

Jonathan