Lookup based on reference
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 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$4 1,1),MATCH(FLOOR($S$1-FLO*OR($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!
|