Possible SUMPRODUCT & HLOOKUP?

[pauleconkle]

Hello and thanks in advance for any help! I have a table below; I then have 3 input cells; Country, City and Grade (the list is essentially 2K rows) so if input cell 'Country' is USA and they select 'NYC' for next input cell...and select 'Grade K B' for the third cell, I need a return of the corresponding amount (11,000). Also, is there a way to then return (in another cell) the next grade amount (so one cell would return 11,000 and the next cell would return 15,000) in the example below. Thanks again!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Grade K A[/TD]
[TD]Grade K B[/TD]
[TD]Grade 1 A[/TD]
[TD]Grade 1B[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]NYC[/TD]
[TD]10,000[/TD]
[TD]11,000[/TD]
[TD]15,000[/TD]
[TD]16,000[/TD]
[/TR]
[TR]
[TD]Ireland[/TD]
[TD]Dublin[/TD]
[TD]5,000[/TD]
[TD]6,000[/TD]
[TD]7,000[/TD]
[TD]8,000[/TD]
[/TR]
[TR]
[TD]Afghanistan[/TD]
[TD]Kabul[/TD]
[TD]1,000[/TD]
[TD]2,000[/TD]
[TD]3,000[/TD]
[TD]4,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Country
[/TD]
[TD]City
[/TD]
[TD]Grade K A
[/TD]
[TD]Grade K B
[/TD]
[TD]Grade 1A
[/TD]
[TD]Grade 1B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]USA
[/TD]
[TD]NYC
[/TD]
[TD]10,000
[/TD]
[TD]11,000
[/TD]
[TD]15,000
[/TD]
[TD]16,000
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Ireland
[/TD]
[TD]Dublin
[/TD]
[TD]5,000
[/TD]
[TD]6,000
[/TD]
[TD]7,000
[/TD]
[TD]8,000
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Afganistan
[/TD]
[TD]Kabul
[/TD]
[TD]1,000
[/TD]
[TD]2,000
[/TD]
[TD]3,000
[/TD]
[TD]4,000
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Country
[/TD]
[TD]USA
[/TD]
[TD]11,000
[/TD]
[TD]15,000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]City
[/TD]
[TD]NYC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Grade
[/TD]
[TD]Grade K B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In C7 enter the array formula. Must be entered with Control+Shift+Enter.

=INDEX(C2:F4,MATCH(B7&B8,A2:A4&B2:B4,0),MATCH(B9,C1:F1,0))

In D7 enter the array formula. Must be entered with Control+Shift+Enter. I assumed if the last column was pulled up so there was no next grade this should be blank.

=IFERROR(INDEX(C2:F4,MATCH(B7&B8,A2:A4&B2:B4,0),MATCH(B9,C1:F1,0)+1),"")

 

[63falcondude]

How's this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Country	City	Grade K A	Grade K B	Grade 1 A	Grade 1B		County	City	Grade	Result	Next grade amount
2	USA	NYC	10,000	11,000	15,000	16,000		USA	NYC	Grade K B	11000	15000
3	Ireland	Dublin	5,000	6,000	7,000	8,000
4	Afghanistan	Kabul	1,000	2,000	3,000	4,000
Sheet1

Cell Formulas
Range	Formula
K2	=SUMPRODUCT((A2:A4=H2)*(B2:B4=I2)*(C1:F1=J2)*(C2:F4))
L2	{=INDEX(C2:F4,MATCH(H2&I2,A2:A4&B2:B4,0),MATCH(J2,C1:F1,0)+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[pauleconkle]

How's this?

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
Country	City	Grade K A	Grade K B	Grade 1 A	Grade 1B
USA	NYC
Ireland	Dublin
Afghanistan	Kabul

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: center"]County[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]Grade[/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]Next grade amount[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10,000[/TD]
[TD="align: right"]11,000[/TD]
[TD="align: right"]15,000[/TD]
[TD="align: right"]16,000[/TD]
[TD="align: right"][/TD]
[TD="align: center"]USA[/TD]
[TD="align: center"]NYC[/TD]
[TD="align: center"]Grade K B[/TD]
[TD="align: center"]11000[/TD]
[TD="align: center"]15000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5,000[/TD]
[TD="align: right"]6,000[/TD]
[TD="align: right"]7,000[/TD]
[TD="align: right"]8,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1,000[/TD]
[TD="align: right"]2,000[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]4,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=SUMPRODUCT((A2:A4=H2)*(B2:B4=I2)*(C1:F1=J2)*(C2:F4))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L2[/TH]
[TD="align: left"]{=INDEX(C2:F4,MATCH(H2&I2,A2:A4&B2:B4,0),MATCH(J2,C1:F1,0)+1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thank you! These worked perfectly!