Multiple criteria lookup

[Excel1991]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Specialty

[/TD]
[TD]Category
[/TD]
[TD]25th
[/TD]
[TD]median
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]spoon
[/TD]
[TD]1000
[/TD]
[TD]1500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]fork
[/TD]
[TD]2000
[/TD]
[TD]300
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]knife
[/TD]
[TD]8000
[/TD]
[TD]1000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]spoon
[/TD]
[TD]5100
[/TD]
[TD]2000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]fork
[/TD]
[TD]6100
[/TD]
[TD]3000

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]knife
[/TD]
[TD]7100
[/TD]
[TD]4000

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am hoping someone can help with an array formula

I am trying to write a formula in excel that will allow me to say “if the specialty is equal to A, look in the category column and find SPOON, and then look into the median or 25^th percentile column and return the value”


Essentially, the specialty column, category column and percentile columns within the formula will change based on what I need to lookup. I have a summary tab set up and im basically trying to look within a table and pull the value that is associated with those three variables. Like I said though, those variables will change (not the column headers, just the value within each column that I am looking for).

Would anyone have anyidea how to accomplish this?

 

[dreid1011]

Perhaps this:

Assuming headers in row 1, data begins in row 2.

For 25th:
=INDEX(C$2:C$7,MATCH($F$2&$G$2,$A$2:$A$7&$B$2:$B$7,0))

For median:
=INDEX(D$2:D$7,MATCH($F$2&$G$2,$A$2:$A$7&$B$2:$B$7,0))

These are array formulas, entered with CTRL+SHIFT+ENTER, not just ENTER.

In the above formulas:

Col A = Specialty
Col B = Category
Col C = 25th
Col D = median

F2 contains the Specialty on your summary sheet, G2 contains the Category on your summary sheet. Adjust all references to suit your data.

 

[jtakw]

Hi,

Not sure if you mean you want Separate results for 25th and median, or you want the combined Total:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Specialty	Category	25th	median		Specialty	Category	25th	median	combined
2	A	spoon	1000	1500		A	spoon	1000	1500	2500
3	A	fork	2000	300		B	fork	6100	3000	9100
4	A	knife	8000	1000
5	B	spoon	5100	2000
6	B	fork	6100	3000
7	B	knife	7100	4000
Sheet228

Cell Formulas
Range	Formula
H2	=SUMIFS(C$2:C$7,A$2:A$7,F2,B$2:B$7,G2)
I2	=SUMIFS(D$2:D$7,A$2:A$7,F2,B$2:B$7,G2)
J2	=SUMPRODUCT((A$2:A$7=F2)*(B$2:B$7=G2)*C$2:D$7)

 

[Excel1991]

Worked like a charm, THANK YOU

 

[Excel1991]

@jtakw, this worked as well, thank you

 

[jtakw]

You're welcome.

 

[dreid1011]

You're welcome.