Vlookup question

[dmash]

Hello,

This sounds like a very simple problem, but I cannot figure out.

Sheet1. In column A, I have a list of partNames, which contains a bunch of letters and numbers with an identifiable 3 character alphabets somewhere. Let's call these identifiable 3 character alphabet: partCategory. The length of the partName and the starting location of the partCategory within each partName vary. A partName may also contain other 3 letter combination which is not a partCategory, but each partName definitely contains a partCategory.

Sheet2. I have a list of all partCategory in one column and its corresponding price in next column.

So in column B of sheet1 I would like to vlookup the price of each partName.

I have this working already using VBA, which basically scans each line of partNames in Sheet1 and puts it in a loop to check against the list of partCategory in Sheet2. I was just wondering if there is a way to do this using an excel formula without using VBA.

Thanks,
Don

 

[HRIS]

Thinking about this I had a couple questions:
-So the partName price is tied to its 3 letter partCategory only?
-If a partName contains another 3 letter sequence, could that sequence ever be the same as any of the partCategories?

 

[dmash]

HRIS - that is correct, the partName price is tied to the 3 letter partCategory only.

Each partName contains 1 and only 1 partCategory. If it contains another 3 letter sequence, it can never be the same as another partCategory. How do they ensure that is another issue altogether, but I'm just working with I have right now.

 

[HRIS]

Maybe try this lookup formula: =LOOKUP(2^15,SEARCH(Sheet2!$D$2:$D$4,A2),Sheet2!$E$2:$E$4)

I tried setting up an example of what you might have and it worked on my example. The "(Sheet2!$D$2:$D$4" part should be the 1st column of your pricing table with the 3 letter sequence. A2 would be the cell you are looking to match up to the pricing table. "Sheet2!$E$2:$E$4)" is the 2nd column of your pricing table - where the actual prices would be.

FYI - I can't take credit for this, rather I saw a post by barry houdini sometime back (on the page linked below) and thought it applied: http://www.mrexcel.com/forum/showthread.php?p=1597483#post1597483. You can find some discussion of the logic somewhere throughout this thread and even some other possible options.

Let me know if this works out for you!

 

[dmash]

Wow, that's genius. I did not know that the lookup function would regard a #VALUE! as a zero. This formula really saves me a lot of computation time. I only need to change the 2^15 to 15 since my partName length cannot exceed 16 characters (i.e. last possible location of the partCategory is 14).

Thanks HRIS for bringing that up, and thanks Barry for coming up with that formula.