VLOOKUP formula assistance required.

I looking for help with a VLOOKUP formula for my spreadsheet. I am looking for the formula to be placed in the 'end wall template' tab. I would like to look up the rcc part number in the 'template' tab 'column B' that have the following criteria:

1) a unique 'detail number' (ie., 201)
2) a 'dash then alpha/number' after the first part the entire part number sequence '-I34V63, -I37V64, -I35V65' (ie., TY2AF-282090-I34V63)

I also don't want blank rows to show up in the 'end wall template'. Out of the 84 part numbers, there is approx 30-40 part numbers that should show up in the 'end wall template' tab because those are the only ones that we need to show the data for. To make this more complicated I need 'columns I - N' to pull the appropriate data related to the correct detail number/rcc part number.

I am trying to have all the data entered into the 'template' and populate the other two tabs in this spreadsheet. The 'datasheet' tab is complete but the 'end wall template' tab is sort of complicated due to only some of the part numbers needing to show data.


I would also like help with taking the number entered into the 'template' tab 'column AC' converted into millimeters in 'column AD'. This is done by multiply AC by 24.5 and this converts inches to millimeters.
FULL-SHIPSET-TEMPLATE-DRAFT.xlsx
Marcia MorrisProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
First off... conversion of mm to inch is 25.4... not 24.5 as stated above.
Justin AlvarezAccount ManagerCommented:
Marcia,

I noticed that you are trying to use a VLOOKUP for multiple criteria (e.g., Detail number must be unique AND the alpha/numeric must be present). It might be a bit tough for VLOOKUP to match against multiple criteria across different columns. You might also run into issues based on the "first match" in VLOOKUP. When the referenced cells have duplicate values, such as the alpha/numeric, the VLOOKUP tends to return the first instance it matches.

Are you familiar with combining functions? Based on the multiple criteria requirement, you might be better served using a combination of INDEX and MATCH.

Some of the more seasoned Excel users might also suggest using VBA code, but I'll see if I can make it work with INDEX MATCH.
Justin AlvarezAccount ManagerCommented:
By the way, I couldn't help but notice that the first column of the "TEMPLATE" sheet all appear to be unique numbers. By that, I mean that the numbers are sequential and there are no duplicates. Do you envision the numbers in the first column to change?

In addition, I also noticed that the 2nd criteria ('dash then alpha/number') only have 3 possibilities. If we were to look at the full RCC Part Number, that would give us 13 truly unique parts:

TY2AF-538580-I36V63
TY2AF-282090-I34V63
TY2AF-198390-I34V63
TY2AF-273540-I34V63
TY2AF-191460-I34V63
TY2AF-282680-I34V63
TY2AF-198860-I34V63
TY1BF-282320-I35V65
TY1BF-198620-I35V65
TY2AF-198980-I34V63
TY2AF-538860-I36V63
TY2AF-538700-I36V63
TY3F-539090-I37V64

Do you have any other criteria for the listing? Or could you point out the 30 or so records that should show up in the "END WALL TEMPLATE" Sheet?

Thanks
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Marcia MorrisProject ManagerAuthor Commented:
justin_alvarez,.. the part numbers wouldn't ever change, the only thing that could happen but highly unlikely is that new part numbers are added. This is a full shipset of 84 pcs so I'm thinking not a chance in heck. Also, yes I can find out what the 30 or so part numbers are. I will get those tomorrow and post them.
Marcia MorrisProject ManagerAuthor Commented:
justin_alvarez... I've removed all of the part numbers and only left the ones I need provide to the customer in the 'end wall template' tab.
FULL-SHIPSET-TEMPLATE-REVNC-04115.xlsx
dabug80Commented:
Hi Marcia,

I would love to help, but I can't work out what you need. Perhaps place the correct results  you are looking for in the first row of each of the sheets. Mark these in yellow.

Cheers
Justin AlvarezAccount ManagerCommented:
Marcia,

My apologies for the delay. I'm not quite sure if I can get your major request to work (having the "End Wall Template" filled with particular data from the "Template").

With regards to the unit conversion, Excel does have a built-in conversion function so you don't have to worry about entering the correct unit conversion.

=CONVERT(number,from_unit,to_unit)

so, you can enter the following into cell AD9
=CONVERT(AC9,"in","mm")

From there, you should be able to click and drag that to the bottom of your table

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marcia MorrisProject ManagerAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.