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:[TABLE="width: 500"]
<tbody>[TR]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]$40,000[/TD]
[TD]$41,000[/TD]
[TD]$42,000[/TD]
[TD]$43,000[/TD]
[TD]Private[/TD]
[/TR]
[TR]
[TD]$45,000[/TD]
[TD]$46,000[/TD]
[TD]$47,000[/TD]
[TD]$48,000[/TD]
[TD]Corporal[/TD]
[/TR]
[TR]
[TD]$50,000[/TD]
[TD]$51,000[/TD]
[TD]$52,000[/TD]
[TD]$53,000[/TD]
[TD]Sergeant[/TD]
[/TR]
[TR]
[TD]$55,000[/TD]
[TD]$56,000[/TD]
[TD]$57,000[/TD]
[TD]$58,000[/TD]
[TD]Lieutenant[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2010
[/TD]
[TD]2011
[/TD]
[TD]2012
[/TD]
[TD]2013
[/TD]
[TD]Rank
[/TD]
[TD][/TD]
[TD]Year
[/TD]
[TD]Rank
[/TD]
[TD]Salary
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]$40,00
[/TD]
[TD]$41,00
[/TD]
[TD]$42,00
[/TD]
[TD]$43,00
[/TD]
[TD]Private
[/TD]
[TD][/TD]
[TD]2011
[/TD]
[TD]Private
[/TD]
[TD] $41,00
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]$45,00
[/TD]
[TD]$46,00
[/TD]
[TD]$47,00
[/TD]
[TD]$48,00
[/TD]
[TD]Corporal
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]$50,00
[/TD]
[TD]$51,00
[/TD]
[TD]$52,00
[/TD]
[TD]$53,00
[/TD]
[TD]Sergeant
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]$55,00
[/TD]
[TD]$56,00
[/TD]
[TD]$57,00
[/TD]
[TD]$8,00
[/TD]
[TD]Lieutenant
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Heidi_w_]

Thanks so much - it worked! :D

 

[Vidar]

You're welcome Heidi