needle in the hay stack !

[rupana]

Sir,

I have a large data [ resistances of copper wire at various temperatures ] say starting from J13:BO318. On calculation I get a value of resistance for a particular application. Now I need to find the closest / nearest value from the table J13:BO318. One advantage is that I know the column number in the table based on the temperature I choose. In that column only I need to search for the calculated resistance value which is nearest / closest to the value in question.

I could not find a way to resolve it. Can any one please help me? A large Thanks in advance to the gentleman / lady who is going to help me out.
Regards
Rupanagudi Ravi Shankar
Hyderabad
8-June-2015

 

[NARAYANK991]

Hi ,

What is your definition of closest value ? Is it the value in the table which is just less than or equal to the value being searched , or is it the value in the table which is just greater than or equal to the value being searched , or is it the value which is arithmetically closest to the value being searched , which may sometimes be less than and sometimes greater than the value being searched ?

Narayan

 

[Hui]

How about:
Y9: =INDEX(OFFSET($J$13:$J$318,,MATCH($N$9,$K$12:$BO$12,0)), MATCH(MIN(ABS(OFFSET($J$13:$J$318,,MATCH($N$9,$K$12:$BO$12,0)) -$N$8)),ABS(OFFSET($J$13:$J$318,,MATCH($N$9,$K$12:$BO$12,0))-$N$8),0)) Ctrl+Shift+Enter

To find the actual address
=ADDRESS(12+MATCH(Y9,OFFSET($J$13:$J$318,,MATCH($N$9,$K$12:$BO$12,0)),0),10+MATCH($N$9,$K$12:$BO$12,0))

 

[David Evans]

OK - well done Hui for providing Ravi Shankar with an answer - i hope he can entertain us at the Chandoo Holiday Party with a few numbers on his Sitar

 

[rupana]

Sir - Mr. Hui,

The formula given is working fine. If I encounter any issues with the results for the data being generated subsequently, I may seek your guidance further. Thanks a lot for your immediate reply and solution.
Regards
Ravi Shankar Rupanagudi

 

[rupana]

OK - well done Hui for providing Ravi Shankar with an answer - i hope he can entertain us at the Chandoo Holiday Party with a few numbers on his Sitar

Sir Mr David Evans, I can only enjoy Music and am only the name sake of the legendary Sitarist Sri. Pandit Ravi Shankar. I cannot hold the instrument itself far from playing for you guides at Chandoo.org. Trust you will find a way to excuse me for my inability to live upto your expecations.
Regards
Ravi Shankar Rupanagudi

 

[rupana]

Hi ,

What is your definition of closest value ? Is it the value in the table which is just less than or equal to the value being searched , or is it the value in the table which is just greater than or equal to the value being searched , or is it the value which is arithmetically closest to the value being searched , which may sometimes be less than and sometimes greater than the value being searched ?

Narayan

Sir - Mr. Narayan, Yes it is "the value which is arithmetically closest to the value being searched , which may sometimes be less than and sometimes greater than the value being searched " is what I am looking for.
Regards
Ravi Shankar Rupanagudi