If functions and Vlookups combined

[Mick Henn]

Hi All,

This might appear to be a long winded question and probably has quite a
simple answer.

I’m trying to create a template in excel for the creation of BOMs (bills of
materials). It’s essentially a list of components partnumbers which belong
are built under a top level SKU number. Beside each BOM I have tried to build
a number of formulae which will do a sanity check on the contents to ensure
they belong to the partnumber which they are being matched to. This part has
been simple, in my work book there are 3 sheets, one with the BOMs on it,
another with an extract from of component attributes and another with
toplevel attributes.

The functions which am using do a Vlookup of the component partnumber in
it’s extract sheet, check a particular column, then vlookup the top level
partnumber in it’s extract sheet and check the same column. And IF the 2
values are equal the formula returns a null but if not, returns text – “Check
Attributesâ€.

However, some components are common to every BOM and as such cannot have
attributes which are connected to each toplevel partnumber. My idea was to
create a table of all generic components in a separate (hidden) worksheet,
and for the function check this list first before performing any of the above
checks. That way the formula would first check if the component was generic,
and if not check if it’s attributes were suitable for the top level SKU.

Below is the formula I’ve created do date, it checks if the component is
generic and if it is it returns “Generic†(as I wanted) however, if the
component is not generic I receive a “#N/A†error.

Any input you can provide would be greatly appreciated and apologies again
for the verbose.



=IF(VLOOKUP(COMPONENT PARTUMBER,GENERIC COMPONENT LIST,1,FALSE)=COMPONENT
PARTNUMBER,"Generic",IF((VLOOKUP(COMPONENT PARTNUMBER,COMPONENT EXTRACT
SHEET,3,FALSE))=VLOOKUP(TOP LEVEL SKU NUMBER,TOP LEVEL SKU EXTRACT
,3,FALSE),â€â€,â€Check Attributesâ€))

 

[JLatham]

The problem, I believe, is that if any of the VLOOKUP() operations ends up
with a no match (#N/A!) then that pretty much overrides everything else in
one fashion or another. You need to be trapping for those. I rewrote things
a little bit and I think this monstrosity will come close to doing what you
want, once you change the names/addresses of the test cells and tables
properly.

=IF(ISNA(VLOOKUP(ComponentPartNumber,GenericComponentList,1,FALSE)),IF(IF(ISNA(VLOOKUP(ComponentPartNumber,ComponentExtractSheet,3,FALSE)),"NoMatch1",VLOOKUP(ComponentPartNumber,ComponentExtractSheet,3,FALSE))=IF(ISNA(VLOOKUP(TopLevelSKUNumber,TopLevelSKUExtract,3,FALSE)),"NoMatch2",VLOOKUP(TopLevelSKUNumber,TopLevelSKUExtract,3,FALSE)),"","Check Attributes"),"Generic")

The ISNA() function traps specifically for a no match found situation in the
associated VLOOKUP(). And by setting one of those results to "NoMatch1" and
the other to "NoMatch2", then when we do our test for equality, there's no
chance for a match if either was #N/A!.

I can't swear by this formula, but at least it went into a cell without
error, so the general syntax is correct at least. But I didn't have actual
data to test with, so I don't know if the final result is correct or not.

Hope that if it doesn't exactly hit the nail on the head that it at least
leads you in the right direction.

 

[Mick Henn]

Many thanks for taking the time to reply, i've copied your formula and added
in my parameters but it still seems to give an "#N/A" error when the
component cannot be found in the generic component list.

I'm sure it just needs a tweek here or there.
Again, Thanks a lot!
Mick.

 

[JLatham]

If you can't get it tweaked up, if you want to send me a workbook with sample
tables and a little data in it, I'd give a shot at getting it to work right.
The main reason I only gave you an "academic" version of it without testing
that it works is that I'm not sure how your tables are set up and what the
data in them looks like.

 

[Mick Henn]

Wow, that'd be great. How can i send you the file though? Can i attach it
here? Sorry, this is the first time i've posted here...

 

[JLatham]

Sorry, forgot to include the email address - remove the spaces, send email
with the file attached.
Help From @ jlatham site. com