Hi there, may we go back to your answer below.
The result of the formula must be either nylon or rubber, B10:B11.
Whichever one it finds in the range B2:D3 for widget1 or widget2.

Your formula only refers to B10 so it only looks for nylon.
When it is copied down it refers to B11 so it only looks for rubber.

Can it look for nylon or rubber (B10:B11) and return whichever one it finds
in the child parts.

Hope this makes sense, I kinda need to post a spreadsheet to make it clearer.

Many thanks

Richard


If I understand what you want, try this...

This data in the range A2:D3 -
widget1 nylon carton1 tape
widget2 rubber carton2 film

A10 = parent lookup value = widget1

B10:B11 = list of acceptable values

Enter this formula in C10 and copy down to C11:

=IF(COUNT(MATCH(B10,INDEX(B$2:D$3,MATCH(A$10,A$2:A $3,0),0),0)),B10,"error")
...
bill of material;
parent child child child
widget1 nylon carton1 tape
widget2 rubber carton2 film

list of acceptable values
nylon
rubber

if the result of looking up a cell with widget1 returns a value
which
is
in
the list of acceptable values, return value, if not return "error"

So the first cell with the formula would be targeted to return an
acceptable
value from a list of raw materials such as nylon or rubber say.
Another cell would then have a similar formula but with say an
acceptable
value concerning cartons.

Each set of child parts would only ever contain one raw material in
the
acceptable value list, never multiples.

The database is extracted using Microsoft Query from a stock control
program.
There are about 4500 parent parts and upto a maximum of 8 child
parts
per
parent.