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!


--
Jonathan May
------------------------------------------------------------------------
Jonathan May's Profile: http://www.excelforum.com/member.php...o&userid=10924
View this thread: http://www.excelforum.com/showthread...hreadid=502893

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!

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 ,
Domenic 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 ,
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!

It does indeed help.

Your are truely an Excel God!

Many thanks


--
Jonathan May
------------------------------------------------------------------------
Jonathan May's Profile: http://www.excelforum.com/member.php...o&userid=10924
View this thread: http://www.excelforum.com/showthread...hreadid=502893

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!


--
Jonathan May
------------------------------------------------------------------------
Jonathan May's Profile: http://www.excelforum.com/member.php...o&userid=10924
View this thread: http://www.excelforum.com/showthread...hreadid=502893

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 ,
Jonathan May
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


--
Jonathan May
------------------------------------------------------------------------
Jonathan May's Profile: http://www.excelforum.com/member.php...o&userid=10924
View this thread: http://www.excelforum.com/showthread...hreadid=502893