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
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