Lookup from 2 horizontal cells??

[jhucker]

Hi,

I'm currently trying to figure out if I am able to do a lookup based on matching two pieces of horizontal information.

Sheet one (Order form) has colums headed Product, Store, Min, Max
Sheet two (Weighting) is a table with the product at the top and the store as the row header, with the max (which I am looking for) as the data.

Is there a formula I can use to do this? I thought an IF formula but I believe nesting is limited to 7, and I have 10 products and 173 stores.

Sheet one looks like this (but more rows)

Product	Store	Min	Max
A	1	2
B	1	2
B	2	2
C	2	2
D	2	2
D	3	2
E	1	2
E	3	2
E	4	2
F	6	2
G	2	2
H	5	2
I	3	2
I	6	2
J	2	2

<tbody>
</tbody>

Sheet 2 looks a little like this (more rows)

Store	A	B	C	D	E	F	G	H	I	J
1	2	4	4	3	4	2	3	3	3	4
2	2	4	3	3	4	2	4	2	4	2
3	4	4	3	3	2	2	4	3	4	2
4	3	2	2	3	3	2	2	3	2	3
5	4	3	2	3	2	2	3	3	2	2
6	2	3	2	3	2	2	3	3	3	2

<tbody>
</tbody>

Thanks in advance

 

[mick0005]

Are you saying, "Find the max where the store and the product match"?

If so, here is your solution.

[COLOR=#574123]=INDEX([/COLOR][COLOR=Blue]Sheet1!$F$1:$P$7,MATCH([COLOR=Red]Sheet1!B2,Sheet1!$F$1:$F$7,0[/COLOR]),MATCH([COLOR=Red]Sheet1!A2,Sheet1!$F$1:$P$1,0[/COLOR])[/COLOR][COLOR=#574123])[/COLOR]

You can drag it down as far as you need.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Product	Store	Min	Max		Store	A	B	C	D	E	F	G	H	I	J
2	A	1	2	2		1	2	4	4	3	4	2	3	3	3	4
3	B	1	2	4		2	2	4	3	3	4	2	4	2	4	2
4	B	2	2	4		3	4	4	3	3	2	2	4	3	4	2
5	C	2	2	3		4	3	2	2	3	3	2	2	3	2	3
6	D	2	2	3		5	4	3	2	3	2	2	3	3	2	2
7	D	3	2	3		6	2	3	2	3	2	2	3	3	3	2
8	E	1	2	4
9	E	3	2	2
10	E	4	2	3
11	F	6	2	2
12	G	2	2	4
13	H	5	2	3
14	I	3	2	4
15	I	6	2	3
16	J	2	2	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =INDEX(Sheet1!$F$1:$P$7,MATCH(Sheet1!B2,Sheet1!$F$1:$F$7,0),MATCH(Sheet1!A2,Sheet1!$F$1:$P$1,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>