Look up Data by Row criteria and column criteria
 

I am trying to build a formula that looks up an identifyer in a row and a
column, such as the following:

0 1 2 3 4
$250 0.01% 12.50% 12.50% 20.00% 20.00%
$12,500 0.01% 8.33% 8.33% 8.33% 13.33%
$37,500 0.01% 6.25% 6.25% 6.25% 6.25%
$125,000 0.01% 5.00% 5.00% 5.00% 5.00%
$187,500 0.01% 4.17% 4.17% 4.17% 4.17%
$250,000 0.01% 3.57% 3.57% 3.57% 3.57%

I want my formula to see that the dollar amount is 37,500 and it fits under
number 3 so the result should be 6.25%.

Keep in mind I am pulling the 37500 from one cell and the number 3 from
another cell. I want this in one formula and if the result is NA it will
read zero instead.

Thanks for your help.

Look up Data by Row criteria and column criteria
 

This will do it:
http://www.contextures.com/xlFunctio...ml#IndexMatch4

Still confused? Sample file he
http://www.ozgrid.com/forum/showthread.php?t=40162


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jason" wrote:

I am trying to build a formula that looks up an identifyer in a row and a
column, such as the following:

0 1 2 3 4
$250 0.01% 12.50% 12.50% 20.00% 20.00%
$12,500 0.01% 8.33% 8.33% 8.33% 13.33%
$37,500 0.01% 6.25% 6.25% 6.25% 6.25%
$125,000 0.01% 5.00% 5.00% 5.00% 5.00%
$187,500 0.01% 4.17% 4.17% 4.17% 4.17%
$250,000 0.01% 3.57% 3.57% 3.57% 3.57%

I want my formula to see that the dollar amount is 37,500 and it fits under
number 3 so the result should be 6.25%.

Keep in mind I am pulling the 37500 from one cell and the number 3 from
another cell. I want this in one formula and if the result is NA it will
read zero instead.

Thanks for your help.

Look up Data by Row criteria and column criteria
 

I want my formula to see that the dollar amount is 37,500

Will the row lookup value always be an exact match?

Will you ever need to lookup something like 38,992 where there is not an
exact match? If there isn't an exact match what should happen?

--
Biff
Microsoft Excel MVP


"Jason" wrote in message
...
I am trying to build a formula that looks up an identifyer in a row and a
column, such as the following:

0 1 2 3 4
$250 0.01% 12.50% 12.50% 20.00% 20.00%
$12,500 0.01% 8.33% 8.33% 8.33% 13.33%
$37,500 0.01% 6.25% 6.25% 6.25% 6.25%
$125,000 0.01% 5.00% 5.00% 5.00% 5.00%
$187,500 0.01% 4.17% 4.17% 4.17% 4.17%
$250,000 0.01% 3.57% 3.57% 3.57% 3.57%

I want my formula to see that the dollar amount is 37,500 and it fits
under
number 3 so the result should be 6.25%.

Keep in mind I am pulling the 37500 from one cell and the number 3 from
another cell. I want this in one formula and if the result is NA it will
read zero instead.

Thanks for your help.