Look up Data by Row criteria and column criteria
in [Functions]
|
From: Jason on 15 Dec 2009 18:01 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 15 Dec 2009 18:18 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 15 Dec 2009 22:13 >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.
|
Pages: 1 Prev: Replacing spaces with data from another cell Next: I cannot highlight a whole row or column |