Lookup and populate question:

[Pierre]

Have numerous assemblies and wish to pull some labor data from another
sheet.

The source data sheet contains the assembly number, and several
"operation numbers" which contain a labor value (hours).

The operation number can be anywhere from 1 to 18.

Some assemblies can only have 1 operation, and others may have 12

The assembly line which is to be populated will look like this after
data is found:

Assembly: op 1 op 2 op 3 op 4 op 5 op 6 (etc. to
18)
12587-A 2 2.8 8 65


The source data which contains many assemblies would look like:

Assembly Operation Hours
12587-A 1 2
12587-A 3 2.8
12587-A 4 8
12587-A 6 65

All assemblies on the source data list might have completely different
hours and operation numbers, but only one "block" of data.

If a value on the populated data page is not found in the lookup, a
blank is returned

I thought of an array formula to find the grid in question on the
source data page, but the formulas construction is a head scratcher.

TIA for any thougths.
Pierre

 

[Biff]

Hi!

Try this:

This data in the range A2:C5

12587-A 1 2
12587-A 3 2.8
12587-A 4 8
12587-A 6 65

A10 = 12587-A
B10:S10 = 1,2,3,4,5....18

Formula in B11 copied across:

=IF(SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)=0,"",SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5))

This assumes that there will be no duplicates. Such as:

12587-A 1 2
12587-A 1 2.8
12587-A 1 8
12587-A 6 65

Biff

 

[Pierre]

Hi!

Try this:

This data in the range A2:C5



A10 = 12587-A
B10:S10 = 1,2,3,4,5....18

Formula in B11 copied across:

=IF(SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)=0,"",SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5))

This assumes that there will be no duplicates. Such as:


Biff

Biff, you're a genius. Thank you!

Pierre

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff, you're a genius. Thank you!

Pierre