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!

Country	City	Grade K A	Grade K B	Grade 1 A	Grade 1B
USA	NYC	10,000	11,000	15,000	16,000
Ireland	Dublin	5,000	6,000	7,000	8,000
Afghanistan	Kabul	1,000	2,000	3,000	4,000

<tbody>
</tbody>

 

[Scott T]

	A	B	C	D	E	F
1	Country	City	Grade K A	Grade K B	Grade 1A	Grade 1B
2	USA	NYC	10,000	11,000	15,000	16,000
3	Ireland	Dublin	5,000	6,000	7,000	8,000
4	Afganistan	Kabul	1,000	2,000	3,000	4,000
5
6	Country	USA	11,000	15,000
7	City	NYC
8	Grade	Grade K B
9

<tbody>
</tbody>

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
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

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula K2 =SUMPRODUCT((A2:A4=H2)*(B2:B4=I2)*(C1:F1=J2)*(C2:F4)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula L2 {=INDEX(C2:F4,MATCH(H2&I2,A2:A4&B2:B4,0),MATCH(J2,C1:F1,0)+1)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you! These worked perfectly!