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