Vlookup help

Hi Excel Gurus,

I am stuck in a situation where I am not being able to express my problem correctly. Let me try this once.

I have this file where I have some POs in column A of sheet1, I need to get the tracking infos from sheet2. I can use vlookup but the problem is that I have all the PO in format "TKO-247" in sheet1 where as in sheet2 the PO can be in any cell of column A and anywhere also the format can be "TKO/247", "TKO 247", "TKO247".

I hope I was able to make u understand.

Originally Posted by gjlindn

Hi Brajesh. This is not pretty or fast, but should do the trick.

Please Login or Register  to view this content.

Note that this has to be entered as an array formula (CTRL+SHIFT+ENTER)
Also, when there is no match found it will display the value in the first row of column B.

Good luck!

Close enough, I must say.

I thought it did the trick but suddenly have seen some results showing "0". Can u pls check once more.
And yes, its really slow. It takes me around 15 minutes on my core2duo 1gb ram win 7 ultimate system.

Thanks and appreciate ur effort anyways.

Originally Posted by gjlindn

This is a little simpler but it does require listing each of the separators within curly brackets

Please Login or Register  to view this content.

Also, in order to increase speed, I've created a named range for the lookup range used. Thanks!

greg, dont know If i missed something but the result of this formula shows "#NAME".
Sorry If i was not able to understand, a big noob here. :D

Originally Posted by Haseeb A

Or may be this with just ENTER,

=IFERROR(INDEX(Sheet2!B:B,LOOKUP(2^20,MATCH("*"&SUBSTITUTE(A2,"-",{"","-","/"," "})&"*",Sheet2!A:A,0))),"N/A")

Thanks Haseeb, that worked. I am yet to examine it fully but it seems to be perfectly ok. Will reply if I need further improvement here.
Appreciate your effort.

@Haseeb A: can u elaborate the formula a bit more so that I can understand this thing. Just wanna learn new things and dont only want to get a readymade solution. :D

Originally Posted by gjlindn

Hi again Brajesh. Try the formula auditor in excel...it really helps to see what's going on with each of the components. Here's my interpretation of what's going on from the inner most to outer:

Substitutes "-" values in A2 of active sheet with each item in the array {"","-","/"," "}. If A2 contains TKO-147 the result would be TKO147,TKO-147,TKO/147,TKO 147.

Please Login or Register  to view this content.

Match below returns the row number where the values in the curly brackets (results of above) are found. Asterisks are used as wild cards so the array item can be found in any position of the searched text. Lets say that TKO/147 is found in row 3, then the results below would be {#N/A,3,#N/A,#N/A}

Please Login or Register  to view this content.

Lookup...a very useful and under-utilized function, has two ways to be used. In this case it's looking up a value in the array created by the match function above {#N/A,3,#N/A,#N/A} and returns the first result less than 2^20, which is the number of rows in Excel 2007 & 2010 (1,048,576 rows).

Please Login or Register  to view this content.

Index then looks at the result of LOOKUP, which is 3 and returns the value of the specified row of the range specified (Sheet2!B:B)

Please Login or Register  to view this content.

Then of course, if there were an error the function below would return "N/A".

Please Login or Register  to view this content.

Hope that helps...and hope you don't mind my explanation Haseeb! Thanks!

wow, that's amazing explanation. will help me a lot in learning these functions.

Thanks a lot.