From: Jason on
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.
From: ryguy7272 on
This will do it:
http://www.contextures.com/xlFunctions03.html#IndexMatch4

Still confused? Sample file here:
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.
From: T. Valko on
>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" <Jason(a)discussions.microsoft.com> wrote in message
news:ED61C1FD-3A7E-4904-97EB-D90C6D72C0C6(a)microsoft.com...
>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.