Excel Match / Index on multiple values and criteria

[remeng]

Hello,

I have a somewhat complex lookup to perform in a set of BOMs that will provide a single result. I've tried several different operations, but I run into a question about validating a return against a list of PN. I've attached a sample of the problem to this post for reference.

The goal is to search the Parent (lookups sheet) and return the Laminate PN from the (lam PN sheet).

I need to process this by using an intermediary sheet (BOM).

Order of operations:

1) Search Parent value from (lookups sheet - column A) in the Parent value (BOM - column A) which will return an array since there are multiple occurrences of the Parent value.
2) Search the returned array for any Component values (BOM - column C) that start with "09901" which can either return a single value or an array.
3) Search the Component value returns from (BOM - column C) to (lam PN - Column A)
4) Return the single value that is active in (lam PN -column A) into Component (lookups - column E)

I've color coated the values to make it a little easier to follow.

Thank you for the help,

Mike

 

[Andrzejek]

Looks to me like a perfect functionality for UDF (User Defined Function) in VBA, which you can ask for in forum707

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

Hi Andy,

I agree that UDF would work, but I need to keep the file macro free since management will be using it :/

I've tried index / match as well as index / search / filter without success.

Mike

 

[Andrzejek]

I would write a UDF, run it, copy the output of UDF/paste as values (or 'Copy Here as Values Only'), save the file as *.xlsx (macro free version) and give it to your Management.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

For me lookups are not the best tool for this tasks. A couple of years ago MS implemented BI elements in Excel. Power Query is, IMHO, most useful functionality since pivot tables.

In attached workbook:
- tParents, tBOM and tLamPN are tables from the source data,
- queries (PQ, connection only): tParents, tBOM and tLamPN used to transfer excel tables to PQ environment, in case of tBOM added filter for Components starting with required code (basic PQ filter functionality),
- qSupport query, connection only: to join tLamPN and filtered tBOM, exact (internal) join.
- qOutput query, output to 'lookups' sheet (H1): outer join of tParents and qSupport, expanded Componet and Length.

Source tables expand automatically when adding new data. End user has to refresh output table only, it is the price for hiding processing data and five minutes design, using mouse only and editing some names.


combo

 

[SkipVought]

Mike,

Back in the day I did a bit of work using multi-level bills of material (parent-child tables) with multiple models of aircraft, down to prepreg / honeycomb for layup for composite components or plate / bar for machining. Wrote recursive code to generate an indented BOM similar to your table on your BOM sheet. Coded also for Where-Used lists for various components, fabricated parts and assemblies.

By what logic, with the data you supplied, do you arrive at the components on the lookups sheet?

I see 4 items (level 1.0 assemblies/components) that assemble directly into K301200157. And only the last items in their respective lists contains the 2 components you list.

OOPS! Starts with "09901"

Sorry,I missed that

That means you need a Where-Used list. From such a list you can very easily acquire the data you would need for ANY component.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[remeng]

Hi Guys,

I was able to talk with one of our departments and learned that the data was being generated incorrectly... Gotta love those days...

Anyway, one of our engineers was able to extract the data directly from the database using some code that even managers can use

Thanks for the help!