Matching ranks/salaries with dates

[Heidi_w_]

I really hope someone can help me - I've been at this for two days and I'm at a loss...

I'm trying to create a formula that will look up a specific rank's salary on a specific date:

2010	2011	2012	2013	Rank
$40,000	$41,000	$42,000	$43,000	Private
$45,000	$46,000	$47,000	$48,000	Corporal
$50,000	$51,000	$52,000	$53,000	Sergeant
$55,000	$56,000	$57,000	$58,000	Lieutenant

<tbody>
</tbody>

I have to come up with a formula that allows a user to input a rank and a date, that will result in the correct salary being reported. I've managed to come up with a formula that successfully searches through a single year, but not for the entire range...

Essentially this is what I'm trying to do: RANK + YEAR = WHAT SALARY

Help!!

 

[aaalviny]

=index(data_range, match(input_rank, rank_col, 0), match(year, year_row, 0))

note: data_range should not include the header row (year row) and header column, i.e. column with rank

 

[Vidar]

Hi
In I3 use this formula:
=INDEX(A2:D5,MATCH(H2,E2:E5,0),MATCH(G2,A1:D1,0))

	A	B	C	D	E	F	G	H	I
1	2010	2011	2012	2013	Rank		Year	Rank	Salary
2	$40,00	$41,00	$42,00	$43,00	Private		2011	Private	$41,00
3	$45,00	$46,00	$47,00	$48,00	Corporal
4	$50,00	$51,00	$52,00	$53,00	Sergeant
5	$55,00	$56,00	$57,00	$8,00	Lieutenant

<tbody>
</tbody>

 

[Heidi_w_]

Thanks so much - it worked! :D

 

[Vidar]

You're welcome Heidi