Advanced VLookup

[BigRed]

Hi. I am setting up a Lease vs. Purchase analysis, and I
have set up a tax depreciation table. I know how to do a
VLOOKUP that brings back the percentage (e.g., Year 5,
and percentage). The problem is that I don't know how to
do a VLOOKUP on 2 columns (vertical and horizontal
column). E.g,. I need it to look up the vertical column
(Years of Loan), and then I need it to look up the
horizontal column (Months). Can I do that with a
VLOOKUP? I know there is also a HLOOKUP, but I don't
know how to use them together (or if I can). Here is a
sample from my spreadsheet, in which I need a value
looked up to be multipled (which I know how to do this
part).

Year 36mo 60mo 84mo 120mo 180mo 240mo
1 33.33% 20.00% 14.29% 10.00% 5.00% 3.750%
2 44.45% 32.00% 24.49% 18.00% 9.50% 7.219%
3 14.81% 19.20% 17.49% 14.40% 8.55% 6.677%
4 7.41% 11.52% 12.49% 11.52% 7.70% 6.177%
5 11.52% 8.93% 9.22% 6.93% 5.713%

Again, I need the Year to be looked up, AND the months
from above. (Both pieces of criteria have to be meet).

I hope I've explained this OK! Any advice is welcome!!

BR

 

[MS]

Hi,
Try using the index function.
=index(array,row_num,col_num)
This will return the value of the cell

In your example name the used range as data
Index(data,3,4) returns 14.29%.

Hope this helps

Guy

 

[Peo Sjoblom]

One way

=INDEX(Table,MATCH(Year,INDEX(Table,,1)),MATCH(Months,INDEX(Table,1,)))

replace the Table with either your name for the Table or the dimensions i.e.
$A$1:$G$50,
replace Year and Months preferably by 2 cells where you type in the values
(that way you can always leave the formula intact) or by for instance using
your example maybe 3 and 84mo.