Looking for multiple values

[Rise]

I have a value that I'd like to use in a vlookup. In this instance, the value is C12345. However, in the list I'm looking up, it may actually be C12345, CN12345, CP12345, CM12345, or even CS12345 due to different stages in our processes. What I thought I might do is make a formula that checks to see if it has a 2 letters before the numbers, and if it doesn't, i'll sub in the various prefixes and look up all of them with nested if()'s & isna()'s. This is my base formula that doesn't seem to be working at the moment. I think it's because it thinks that the numbers are still text since the cell contains text values (obviously making the cell of the text format).

Cell contains: "C12345"
=IF(ISNUMBER(LEFT($A2, 2)), MID($A2, 2, (LEN($A2)-1)), $A2)

Am I going about this the wrong way?

[HansV]

Is your lookup value the text string C12345, or is it the number 12345 displayed as C12345 because of a custom number format?

And are you only interested in the numeric part (so PQ12345 would also be a match), or is the letter C significant?

[Rise]

My look up is the part number, the C is a prefix that changes throughout the process so it could be at various stages in the process. Technically, it could even be MRP12345, but that is more unlikely. It's also possible that I have a CN12345 in the lookup, but I am looking for the C12345 in the list, it goes both ways due to the archaic way things are put in the system.

I guess it's worth mentioning that there may even be multiple instances (in many cases there definitely are) of each part number in the vlookup data table due to the same part number physically being on multiple racking locations.

[HansV]

Sorry to be a pest, but I need to get this straight. Do you want to match the value 12345, regardless of the prefix in the lookup value and in the lookup list, or do we need to take the prefix, or part of it, into account, and if so, how exactly?

[Rise]

Hans you are never a pest

I would like to match the value 12345, regardless of the prefix.

Issues:
-number length changes, can be 4 or 5 numbers (1234 or 12345)
-prefixes can be 1, 2, 3, or 4 characters long

[HansV]

I think you'll have to use intermediate formulas to extract the numeric part of the strings.
For example, with a string such as PX4838 in cell A2, the following array formula (confirmed with Ctrl+Shift+Enter) will extract the numeric part 4838:

=1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW($1:$10),1)),0),10)

See the attached sample workbook for a way to use these formulas to look up a value regardless of the prefix. The data are shaded green, the intermediate formulas are shaded blue, and the lookup formula is shaded orange.

LookupParts.xlsx

[Rise]

So in my case, I should have a hidden column that splits the number from the prefix as well as a column in the sheet with the data to use that as the vlookup identifier. That should work, I'll try it out!

Is there anyway to list multiple occurences within a vlookup? For instance, if there was a "cc" in addition to the "bb" from your example; could I have the data represented as "bb, cc" ? or is that going into macro territory?

[HansV]

Steve (sdckapr) posted a solution (which does indeed involve VBA) in the Windows Secrets Lounge some time ago: see post #4 in Lookup more than one row (2000).

[Rise]

Steve (sdckapr) posted a solution (which does indeed involve VBA) in the Windows Secrets Lounge some time ago: see post #4 in Lookup more than one row (2000).

wow, that's genius. AND it works perfectly! SO AWESOME!