Lookup - Improved vlookup

[ryland00]

Hello,

So I have been using the following code for situations where I am looking for a specific number string in a group of cells where I can't necessarily use a vlookup because the data is not consistent. (i.e. trying to find number string 034910294, but some cells might say "# 034910294", while others might say "number 034910294", and still others might say "here is number 034910294", etc..

=VLOOKUP(LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND(Key!$G$2:$G$29,$F4),Key!$G$2:$G$29))),Key!G:H,2,0)

This formula has been working wonderfully, however, I am wondering if there is any way to change it slightly so that the user is not limited to just the 28 items in the Key tab (referenced above). I don't trust that the users of my workbook will know that they will not only have to update column G in the 'Key' tab, but they will also need to change the formula each time an update is made from Key!$G$2:$G$29 to Key!$G$2:$G$30, or Key!$G$2:$G$31, etc.

I am not sure if something like this is possible, but I thought I would ask all of you excel geniuses.

Thanks!

Ryan

 

[ryland00]

In case it makes it easier, the section I want the formula to review will always be the second segment :

10-5S6DB1-WD4-5555

 

[Aladin Akyurek]

In case it makes it easier, the section I want the formula to review will always be the second segment :

10-5S6DB1-WD4-5555

Do we want to apply this formula to that bit?

=IFERROR(LOOKUP(BigNum,FIND(Keys,$F4),Values),"not found")

 

[ryland00]

Do we want to apply this formula to that bit?

=IFERROR(LOOKUP(BigNum,FIND(Keys,$F4),Values),"not found")

Yes, that would be ideal. It has to be something with the $F4 part of the formula, right?

 

[Aladin Akyurek]

Yes, that would be ideal. It has to be something with the $F4 part of the formula, right?

Yes. Does this help?

=IFERROR(LOOKUP(BigNum,FIND(Keys,LEFT($F4,FIND("-",$F4,FIND("-",$F4)+1)-1)),Values),"not found")

 

[ryland00]

I get the message "not found" for everything now.

 

[ryland00]

I get the message "not found" for everything now.

I think I've got it, unless you tell me differently. I removed the "-1" from the formula and it looks like it's working correctly..

If so, thanks so much for your help!!

 

[Aladin Akyurek]

I think I've got it, unless you tell me differently. I removed the "-1" from the formula and it looks like it's working correctly..

If so, thanks so much for your help!!

The LEFT($F4,FIND("-",$F4,FIND("-",$F4)+1)-1) bit would deliver:

10-FINTRAIN

If you take out -1, we get:

10-FINTRAIN-

So, that's probably what you need to look at.