Lookup based on reference
 

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-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!

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!

Lookup based on reference
 

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" wrote in message
...
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-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!

Lookup based on reference
 

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$4 1,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!

Lookup based on reference
 

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$4 1,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!

Lookup based on reference
 

Doug,
You still have not given us the complete picture
The number that is closest to 0.21 is 0.2019 in the 0.4 column of the 0.5
row
How are we to know which row or column to search?
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Doug" wrote in message
...
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$4 1,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!