Forum Discussion
Solved
INDEX...retrun the closesed value
I try to have returned the value coming most closeley to the value in the matrix: Specifically, I try to define an insulation thickness of the pipe, depending on diameter and operating temperature :...
=INDEX($C$2:$I$11,MATCH(B15,$B$2:$B$11,0),MATCH(B16,$C$1:$I$1,-1))
Maybe with this formula as shown in the attached file.
HiHelmut0806 ,
If you order the values in ascending order, I can think of a solution in two steps: list intermediate values, and search them with HLOOKUP.
Kind regards
Hans
Dankeschön!
First solution helped already; it was the "-1" missing 😉
First solution helped already; it was the "-1" missing 😉
-1 returns next smaller item, not the closest one. If you are 365 or 2021 that could be like
=XLOOKUP( 0, ABS( $B$17 - $B$3:$B$14), XLOOKUP(0,ABS(B16-$C$2:$L$2),$C$3:$L$14,,1),,1 )for
or its equivalent for previous Excel versions.
