SOLVED

New Contributor

# 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 :

How can I define the formular so that a thickness is defined, if the operating value does not exactly match any of the values of the matrix, but is between two of them?

example: if Ø508 and Temp. 380, the vlaue to be returned should be 180

Thank you!

helmut

5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: INDEX...retrun the closesed value

=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.

thank you!

# Re: INDEX...retrun the closesed value

Hi@Helmut0806 ,

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

# Re: INDEX...retrun the closesed value

Dankeschön!
First solution helped already; it was the "-1" missing

# Re: INDEX...retrun the closesed value

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