Look up formula based on two criteria

[chmof13]

Good afternoon,

I need some assistance on finding a formula! Jumping straight to the point, here is my predicament:

[TABLE="width: 724"]
<tbody>[TR]
[TD]Customer #[/TD]
[TD]Customer Name[/TD]
[TD]Master Price Level[/TD]
[TD]0001[/TD]
[TD]0002[/TD]
[TD]0003[/TD]
[TD]0004[/TD]
[TD]0005[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]apple[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]ibm[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]dell[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am trying to look up a value under 0001 (D2), 0002 (E2), etc. but it also has to look at Customer # to pick the correct value. For example, customer x will have info under 0001 and 0004 on different cells (same column) but I need those values to be posted with a formula..

Any help would be appreciated!!

 

[Scott Huish]

You didn't have any values under 0001, 0002 etc. so I put some in this sample.
This is usually called an INDEX MATCH MATCH. See below.

Excel 2010
	A	B	C	D	E	F	G	H
1	Customer #	Customer Name	Master Price Level	0001	0002	0003	0004	0005
2	x	apple	1	a	b	c	d	e
3	y	ibm	2	f	g	h	i	j
4	z	dell	3	k	lm	n	o	p
5
6
7
8		x	0001	a
Sheet1

Cell Formulas
Range	Formula
D8	=INDEX($A$1:$H$4,MATCH(B8,$A$1:$A$4,0),MATCH(C8,$A$1:$H$1,0))

 

[chmof13]

Scott,

Thank you for the reply! I did not put any values under there because their is no values under there currently, that's what I need the formulas for, do display that data. Here is a sample of the file that I will be pulling information from (it is another sheet in the workbook):


[TABLE="width: 387"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]CustomerNo[/TD]
[TD]CustomerName[/TD]
[TD]ProductLine[/TD]
[TD](DATA NEEDED)[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]apple[/TD]
[TD="align: right"]0001[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]apple[/TD]
[TD="align: right"]0003[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]ibm[/TD]
[TD="align: right"]0001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]ibm[/TD]
[TD="align: right"]0002[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]dell[/TD]
[TD="align: right"]0004[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]dell[/TD]
[TD="align: right"]0005[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]


So the data needed tab will fill in horizontally on the customer name row. Does this make sense?

 

[jtakw]

Hi,

What you posted in Post #3 was never mentioned in OP, and should have been included, so that helpers can understand what you're asking.

Now, if I understand correctly, use D2 formula copied down and across.
If you Don't want 0 (zero)s to show, use D7 formula copied down and across.
Change cell references/range to match your data, add the "other" sheet name to the formulas.

Book1
	A	B	C	D	E	F	G	H
1	Customer #	Customer Name	Master Price Level	0001	0002	0003	0004	0005
2	x	apple	1	2	0	3	0	0
3	y	ibm	2	1	2	0	0	0
4	z	dell	3	0	0	0	3	4
5
6	Customer #	Customer Name	Master Price Level	0001	0002	0003	0004	0005
7	x	apple	1	2		3
8	y	ibm	2	1	2
9	z	dell	3				3	4
10
11	CustomerNo	CustomerName	ProductLine	(DATA NEEDED)
12	x	apple	0001	2
13	x	apple	0003	3
14	y	ibm	0001	1
15	y	ibm	0002	2
16	z	dell	0004	3
17	z	dell	0005	4
Sheet196

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(($A$12:$A$17=$A2)*($C$12:$C$17=D$1)*$D$12:$D$17)
D7	=IF(SUMPRODUCT(($A$12:$A$17=$A7)*($C$12:$C$17=D$1)*$D$12:$D$17)=0,"",SUMPRODUCT(($A$12:$A$17=$A7)*($C$12:$C$17=D$1)*$D$12:$D$17))