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

 

[mrmmickle1]

Whenever, the user updates the Key worksheet with an additional row, why don't you just trigger a worksheet event that will update the Vlookup formula with the new row number?

 

[Aladin Akyurek]

Define the following in Name Manager:

BigStr as referring to:

=REPT("z",255)

BigNum as referring to:

=9.99999999999999E+307

Data as referring to:

=Key!$G$2:INDEX(Key!$H:$H,MATCH(BigStr,Key!$G:$G))

Keys as referring to:

=INDEX(Data,0,1)

Values as referring to:

=INDEX(Data,0,2)

The VLOOKUP formula that you have now becomes:

=VLOOKUP(LOOKUP(BigStr,CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(BigNum,FIND(Keys,$F4),Values))),Key!G:H,2,0)

It looks like Key!G:H is the same data as Data we defined above. I wonder whether just the following wouldn't suffice?

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

 

[ryland00]

Define the following in Name Manager:

BigStr as referring to:

=REPT("z",255)

BigNum as referring to:

=9.99999999999999E+307

Data as referring to:

=Key!$G$2:INDEX(Key!$H:$H,MATCH(BigStr,Key!$G:$G))

Keys as referring to:

=INDEX(Data,0,1)

Values as referring to:

=INDEX(Data,0,2)

The VLOOKUP formula that you have now becomes:

=VLOOKUP(LOOKUP(BigStr,CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(BigNum,FIND(Keys,$F4),Values))),Key!G:H,2,0)

It looks like Key!G:H is the same data as Data we defined above. I wonder whether just the following wouldn't suffice?

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

While both methods worked, I prefer the formula driven one.

Thank you so much as always Aladin, you are correct, the updated formula worked wonders!!!

 

[Aladin Akyurek]

While both methods worked, I prefer the formula driven one.

Thank you so much as always Aladin, you are correct, the updated formula worked wonders!!!

Great to hear that it worked out. Thanks for the kind update.

 

[TotallyConfused]

Hello Ryland00 and Aladin

I’ve been following this thread with great interest, however, being very much a beginner, some of the commands you used I don’t fully understand yet. That fact may be the answer to my questions. Because I didn’t want to ‘steal’ your thread Ryland, I waited until it seems you have received a satisfactory answer to your problem before writing this letter.

Ryland, you mentioned in your opening post that one thing you would like to have happen, would be to have the range of data automatically extended as new data was added, so that the user didn’t have to make any adjustments to the data range or the formula. I know how to adjust the size of a data range using VBA. In the formula suggested by Aladin, does this happen, and if so, what part of the formula does that?

My second question, and this is one I worked on to see if I could come up with a solution, concerns the problem that some of the numbers you are searching have leading characters. I tried various combinations of using the RIGHT command embedded within VLOOKUP to strip away any leading characters, but I was never successful. How and where does your code deal with these leading characters?

 

[Aladin Akyurek]

[...]

Ryland, you mentioned in your opening post that one thing you would like to have happen, would be to have the range of data automatically extended as new data was added, so that the user didn’t have to make any adjustments to the data range or the formula. I know how to adjust the size of a data range using VBA. In the formula suggested by Aladin, does this happen, and if so, what part of the formula does that?

=Key!$G$2:INDEX(Key!$H:$H,MATCH(BigStr,Key!$G:$G))

defines Data, running from Key!$G$2 to the last data row in column H. The last data row is determined with the MATCH bit:

MATCH(BigStr,Key!$G:$G)

makes MATCH land on the last row with text in column G. (MATCH(BigNum,NumericRange) would land on the row of the last numeric value of NumericRange.)

See:
9.9999999 .... ?
https://www.mrexcel.com/forum/excel...tiple-matches-match-returned.html#post1523998

My second question, and this is one I worked on to see if I could come up with a solution, concerns the problem that some of the numbers you are searching have leading characters. I tried various combinations of using the RIGHT command embedded within VLOOKUP to strip away any leading characters, but I was never successful. How and where does your code deal with these leading characters?

See: https://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html#post3568323

 

[TotallyConfused]

Hello Aladin

I want to THANK YOU for taking the time and effort to not only point out the sections of your code that answers my questions, but also to briefly explain how they accomplish the results. I’ve worked my way through some of your answers, but now it is time for me to head off to work. This thread has been saved in my ‘Subscriptions’ section since its beginning, so I’ll definitely be back to study your answers. I’ll be sure to check out those links you provided too. THANKS again. You’ve been a big help, not only to me but to Ryland, I’m sure.

 

[Aladin Akyurek]

@TotallyConfused

You are welcome. Thanks for providing feedback.

 

[ryland00]

Hey Aladin,

Running into an issue with the formula and wondering if you might have a fix for it... While the formula is working wonderfully, there is 1 issue I am running into regularly.

On my key tab, one of "values" that can potentially be brought in is "B1". Where this causes issues is in what the formula is checking.

So for example, if the user enters

10-FINTRAIN-WB1-5555 = In this scenario, I would WANT the formula to bring in "Train", however, it is bringing in "B1".

10-AGILB1-W36-5555 = In this scenario, I would want the formula to bring in "B1", as the second segment is the part of the task where it tells me what type of work the user is performing.

Any idea how to make this work?!

Appreciate your help as always.

Ryan