Lookup based on reference
in [Programming]
|
Prev: Macro Help
Next: DO Until Loop
From: Doug on 30 Apr 2010 09:17 The other day, I used this formula to lookup table data based on the row and column such as: =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0)) So that If I had the number 2.22, it will return for me .15 from the table lookup table (made up values) .00 .01 .02 .03 .04 ... .09 2.0 .31 .32 .33 .34 .35 ... .50 2.1 .21 .35 .51 .51 .25 ... .85 2.2 .53 .52 .15 .52 .51 ... .81 2.3 .89 .58 .23 .45 .15 ... .15 My question now is, based on the above formula, how would I be able to do the opposite. So if I have the indexed value; say .15, and I want it to return 2.22? -- Thank you!
From: AB on 30 Apr 2010 09:42 How the formula is supposed to know whether the .15 'means' 2.22 or 2.39 (as they both would return .15)? On Apr 30, 2:17 pm, Doug <D...(a)discussions.microsoft.com> wrote: > The other day, I used this formula to lookup table data based on the row and > column such as: > =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0)) > > So that If I had the number 2.22, it will return for me .15 from the table > lookup table (made up values) > .00 .01 .02 .03 .04 ... .09 > 2.0 .31 .32 .33 .34 .35 ... .50 > 2.1 .21 .35 .51 .51 .25 ... .85 > 2.2 .53 .52 .15 .52 .51 ... .81 > 2.3 .89 .58 .23 .45 .15 ... .15 > > My question now is, based on the above formula, how would I be able to do > the opposite. So if I have the indexed value; say .15, and I want it to > return 2.22? > > -- > Thank you!
From: Bernard Liengme on 30 Apr 2010 09:48 I think we need more. Suppose the know value was 0.51 (which occurs more than once), would you want 2.1 or 2.2 returned? best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Doug" <Doug(a)discussions.microsoft.com> wrote in message news:F4EDE8F6-29F5-4799-A833-4CC84A3BD87A(a)microsoft.com... > The other day, I used this formula to lookup table data based on the row > and > column such as: > =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0)) > > So that If I had the number 2.22, it will return for me .15 from the table > lookup table (made up values) > .00 .01 .02 .03 .04 ... .09 > 2.0 .31 .32 .33 .34 .35 ... .50 > 2.1 .21 .35 .51 .51 .25 ... .85 > 2.2 .53 .52 .15 .52 .51 ... .81 > 2.3 .89 .58 .23 .45 .15 ... .15 > > My question now is, based on the above formula, how would I be able to do > the opposite. So if I have the indexed value; say .15, and I want it to > return 2.22? > > -- > Thank you!
From: Jacob Skaria on 30 Apr 2010 10:35 Array entered with query value in cell T1 =INDEX(Q:Q,MIN(IF($R$3:$AA$41=T1,ROW($R$3:$AA$41))))+ INDEX(2:2,MIN(IF($R$3:$AA$41=T1,COLUMN($R$3:$AA$41)))) -- Jacob (MVP - Excel) "Doug" wrote: > The other day, I used this formula to lookup table data based on the row and > column such as: > =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0)) > > So that If I had the number 2.22, it will return for me .15 from the table > lookup table (made up values) > .00 .01 .02 .03 .04 ... .09 > 2.0 .31 .32 .33 .34 .35 ... .50 > 2.1 .21 .35 .51 .51 .25 ... .85 > 2.2 .53 .52 .15 .52 .51 ... .81 > 2.3 .89 .58 .23 .45 .15 ... .15 > > My question now is, based on the above formula, how would I be able to do > the opposite. So if I have the indexed value; say .15, and I want it to > return 2.22? > > -- > Thank you!
From: Doug on 30 Apr 2010 11:41
I am sorry, let me give more detail. These are the actual values in a portion of the table; If cell "T1" says ".12" I need it to first recognize the closest value, being between 0.1179 & 0.1217. Second since it is closer to 0.1217 it would return the value ".31". How can this be accomplished please? 0.00 (0.01) 0.02 0.03 0.0 0.0000 0.0040 0.0080 0.0120 0.1 0.0398 0.0438 0.0478 0.0517 0.2 0.0793 0.0832 0.0871 0.0910 (0.3) 0.1179 (0.1217) 0.1255 0.1293 0.4 0.1554 0.1591 0.1628 0.1664 0.5 0.1915 0.1950 0.1985 0.2019 0.6 0.2257 0.2291 0.2324 0.2357 0.7 0.2580 0.2611 0.2642 0.2673 0.8 0.2881 0.2910 0.2939 0.2967 0.9 0.3159 0.3186 0.3212 0.3238 1.0 0.3413 0.3438 0.3461 0.3485 1.1 0.3643 0.3665 0.3686 0.3708 -- Thank you! "Jacob Skaria" wrote: > Array entered with query value in cell T1 > > =INDEX(Q:Q,MIN(IF($R$3:$AA$41=T1,ROW($R$3:$AA$41))))+ > INDEX(2:2,MIN(IF($R$3:$AA$41=T1,COLUMN($R$3:$AA$41)))) > > -- > Jacob (MVP - Excel) > > > "Doug" wrote: > > > The other day, I used this formula to lookup table data based on the row and > > column such as: > > =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0)) > > > > So that If I had the number 2.22, it will return for me .15 from the table > > lookup table (made up values) > > .00 .01 .02 .03 .04 ... .09 > > 2.0 .31 .32 .33 .34 .35 ... .50 > > 2.1 .21 .35 .51 .51 .25 ... .85 > > 2.2 .53 .52 .15 .52 .51 ... .81 > > 2.3 .89 .58 .23 .45 .15 ... .15 > > > > My question now is, based on the above formula, how would I be able to do > > the opposite. So if I have the indexed value; say .15, and I want it to > > return 2.22? > > > > -- > > Thank you! |