Lookup in Table based on Two Conditions

[dme82]

I have a table which contains constant values depending on interest rate and
loan term. The top row of the table lists the interest rates and the first
colum lists the loan term such as follows:

7.50% 8.00% 8.25%
5 .240 .243 .245
8 .167 .170 .171
10 .142 .146 .147

I want to be able to enter an interest rate in one cell, a loan term in
another cell, and then have a formula in another cell return the
corresponding constant. For example, if I enter 7.50% in cell A1, 10 in cell
A2, I want a formula in cell A3 to return .142.

Any help would be greatly appreciated!

 

[Niek Otten]

With your table (including headers) in B6:E0:

=INDEX(C7:E9,MATCH(A2,B7:B9,0),MATCH(A1,C6:E6,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a table which contains constant values depending on interest rate and
| loan term. The top row of the table lists the interest rates and the first
| colum lists the loan term such as follows:
|
| 7.50% 8.00% 8.25%
| 5 .240 .243 .245
| 8 .167 .170 .171
| 10 .142 .146 .147
|
| I want to be able to enter an interest rate in one cell, a loan term in
| another cell, and then have a formula in another cell return the
| corresponding constant. For example, if I enter 7.50% in cell A1, 10 in cell
| A2, I want a formula in cell A3 to return .142.
|
| Any help would be greatly appreciated!

 

[CLR]

=HLOOKUP(A1,F1:H4,MATCH(B1,E1:E4),0)

Vaya con Dios,
Chuck, CABGx3