Complex Lookup
 

I'm trying to create something like a cross-table, I need a lookup
function for the grid.

I have a list of data pairings on a worksheet ("FILTERED DATA")
ASSY is in column D
SKU is in Column H
(each of these pairs will be unique)

I have a grid on a worksheet ("MEDIA GRID")
Column D is ASSY
Row 2 is SKU
Row 1 is the qty of each sku required (this data is a vlookup from
another worksheet)

For each space in the grid I want to check to see if there is an
instance of the pair in "FILTERED DATA". If there is then I want the
space to return the value in row 1 of MEDIA GRID. There are going to
be about 45000 grid spaces.

Thanks,
Jason Lepack

Complex Lookup
 

Assuming your media table appears as below. Assy is in D1:D100 and sku's are
in H1:H100 on the Filtered Data worksheet.

D E F
1 Qty Qty
2 sku# sku#
3 Assy
4 Assy
5 Assy

try this in E3 (change the false argument for the if statement if you want
to return something other than 0):

=If(Sumproduct(--('Filtered Data'!$D$1:$D$100=$D3), --('Filtered
Data'!$H$1:$H$100=E$2)), E$1, 0)

and copy down and across.


"Jason Lepack" wrote:

I'm trying to create something like a cross-table, I need a lookup
function for the grid.

I have a list of data pairings on a worksheet ("FILTERED DATA")
ASSY is in column D
SKU is in Column H
(each of these pairs will be unique)

I have a grid on a worksheet ("MEDIA GRID")
Column D is ASSY
Row 2 is SKU
Row 1 is the qty of each sku required (this data is a vlookup from
another worksheet)

For each space in the grid I want to check to see if there is an
instance of the pair in "FILTERED DATA". If there is then I want the
space to return the value in row 1 of MEDIA GRID. There are going to
be about 45000 grid spaces.

Thanks,
Jason Lepack

Complex Lookup
 

There were too many calculations, I decided to programmatically create
the data from my list (less comparisons) and placec the value in Row 1
from Media Grid and put it in at the intersection of the assy and the
sku.

Thanks for your help.

Cheers,
Jason Lepack

On Feb 2, 5:14 pm, JMB wrote:
Assuming your media table appears as below. Assy is in D1:D100 and sku's are
in H1:H100 on the Filtered Data worksheet.

D E F
1 Qty Qty
2 sku# sku#
3 Assy
4 Assy
5 Assy

try this in E3 (change the false argument for the if statement if you want
to return something other than 0):

=If(Sumproduct(--('Filtered Data'!$D$1:$D$100=$D3), --('Filtered
Data'!$H$1:$H$100=E$2)), E$1, 0)

and copy down and across.

"Jason Lepack" wrote:
I'm trying to create something like a cross-table, I need a lookup
function for the grid.

I have a list of data pairings on a worksheet ("FILTERED DATA")
ASSY is in column D
SKU is in Column H
(each of these pairs will be unique)

I have a grid on a worksheet ("MEDIA GRID")
Column D is ASSY
Row 2 is SKU
Row 1 is the qty of each sku required (this data is a vlookup from
another worksheet)

For each space in the grid I want to check to see if there is an
instance of the pair in "FILTERED DATA". If there is then I want the
space to return the value in row 1 of MEDIA GRID. There are going to
be about 45000 grid spaces.

Thanks,
Jason Lepack