use of vllokup and find nearest value functions togather

S

saji04

New Member
Joined
Jul 24, 2013
Messages
18
Hi, Please help me to suggest a formula for the following query

from the table below I want to find the nearest value from the row which is found by vlookup of index function. (Vlookup/index function is to find value from column A)

[TABLE="width: 512"]
<colgroup><col style="width:48pt" span="8" width="64"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 576"]
<colgroup><col style="width:48pt" span="9" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 640"]
<colgroup><col style="width:48pt" span="10" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[TD="class: xl66, width: 64"]D[/TD]
[TD="class: xl66, width: 64"]E[/TD]
[TD="class: xl66, width: 64"]F[/TD]
[TD="class: xl66, width: 64"]G[/TD]
[TD="class: xl66, width: 64"]H[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.363[/TD]
[TD="align: right"]0.736[/TD]
[TD="align: right"]0.92[/TD]
[TD="align: right"]0.981[/TD]
[TD="align: right"]0.996[/TD]
[TD="align: right"]0.999[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]0.333[/TD]
[TD="align: right"]0.699[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]0.974[/TD]
[TD="align: right"]0.995[/TD]
[TD="align: right"]0.999[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]0.301[/TD]
[TD="align: right"]0.663[/TD]
[TD="align: right"]0.879[/TD]
[TD="align: right"]0.96[/TD]
[TD="align: right"]0.992[/TD]
[TD="align: right"]0.999[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]0.273[/TD]
[TD="align: right"]0.627[/TD]
[TD="align: right"]0.857[/TD]
[TD="align: right"]0.957[/TD]
[TD="align: right"]0.989[/TD]
[TD="align: right"]0.998[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]0.247[/TD]
[TD="align: right"]0.592[/TD]
[TD="align: right"]0.833[/TD]
[TD="align: right"]0.946[/TD]
[TD="align: right"]0.986[/TD]
[TD="align: right"]0.997[/TD]
[TD="align: right"]0.999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.223[/TD]
[TD="align: right"]0.558[/TD]
[TD="align: right"]0.809[/TD]
[TD="align: right"]0.934[/TD]
[TD="align: right"]0.981[/TD]
[TD="align: right"]0.996[/TD]
[TD="align: right"]0.999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]0.202[/TD]
[TD="align: right"]0.525[/TD]
[TD="align: right"]0.783[/TD]
[TD="align: right"]0.921[/TD]
[TD="align: right"]0.976[/TD]
[TD="align: right"]0.994[/TD]
[TD="align: right"]0.999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1.7[/TD]
[TD="align: right"]0.183[/TD]
[TD="align: right"]0.493[/TD]
[TD="align: right"]0.757[/TD]
[TD="align: right"]0.907[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.992[/TD]
[TD="align: right"]0.998[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]1.8[/TD]
[TD="align: right"]0.165[/TD]
[TD="align: right"]0.463[/TD]
[TD="align: right"]0.731[/TD]
[TD="align: right"]0.891[/TD]
[TD="align: right"]0.964[/TD]
[TD="align: right"]0.99[/TD]
[TD="align: right"]0.997[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1.9[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]0.434[/TD]
[TD="align: right"]0.704[/TD]
[TD="align: right"]0.875[/TD]
[TD="align: right"]0.956[/TD]
[TD="align: right"]0.987[/TD]
[TD="align: right"]0.997[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
You will need to give us a little more than that to go on...

Rather than specifying how you want to do it, could you describe exactly what you want the formula to deliver?

Matty
 
Upvote 0
Matty said:
You will need to give us a little more than that to go on...

Rather than specifying how you want to do it, could you describe exactly what you want the formula to deliver?

Matty
Click to expand...

Hi, First of all Thanks for your reply

In this case I am having reference values for column A ie. ranging from .005 to 10 and each value corresponds to another value ranging from .016 to 1. Now I find a nearest value of 0.80 from a row for value 1.5. I want to get a result 21 which is mentioned at first row just below column D. Updated table is attached below

[TABLE="width: 640"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A
[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C
[/TD]
[TD="class: xl66, width: 64"]D
[/TD]
[TD="class: xl66, width: 64"]E
[/TD]
[TD="class: xl66, width: 64"]F
[/TD]
[TD="class: xl66, width: 64"]G
[/TD]
[TD="class: xl66, width: 64"]H
[/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD] 1
[/TD]
[TD]14
[/TD]
[TD="class: xl67"]18
[/TD]
[TD="class: xl67"]20
[/TD]
[TD="class: xl67"]21
[/TD]
[TD="class: xl67"]29
[/TD]
[TD="class: xl67"]31
[/TD]
[TD="class: xl67"]33
[/TD]
[TD="class: xl67"]35
[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.363[/TD]
[TD="align: right"]0.736[/TD]
[TD="align: right"]0.92[/TD]
[TD="align: right"]0.981[/TD]
[TD="align: right"]0.996[/TD]
[TD="align: right"]0.999[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]0.333[/TD]
[TD="align: right"]0.699[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]0.974[/TD]
[TD="align: right"]0.995[/TD]
[TD="align: right"]0.999[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]0.301[/TD]
[TD="align: right"]0.663[/TD]
[TD="align: right"]0.879[/TD]
[TD="align: right"]0.96[/TD]
[TD="align: right"]0.992[/TD]
[TD="align: right"]0.999[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]0.273[/TD]
[TD="align: right"]0.627[/TD]
[TD="align: right"]0.857[/TD]
[TD="align: right"]0.957[/TD]
[TD="align: right"]0.989[/TD]
[TD="align: right"]0.998[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]0.247[/TD]
[TD="align: right"]0.592[/TD]
[TD="align: right"]0.833[/TD]
[TD="align: right"]0.946[/TD]
[TD="align: right"]0.986[/TD]
[TD="align: right"]0.997[/TD]
[TD="align: right"]0.999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]1.5
[/TD]
[TD="align: right"]0.223[/TD]
[TD="align: right"]0.558[/TD]
[TD="align: right"]0.809
[/TD]
[TD="align: right"]0.934[/TD]
[TD="align: right"]0.981[/TD]
[TD="align: right"]0.996[/TD]
[TD="align: right"]0.999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]0.202[/TD]
[TD="align: right"]0.525[/TD]
[TD="align: right"]0.783[/TD]
[TD="align: right"]0.921[/TD]
[TD="align: right"]0.976[/TD]
[TD="align: right"]0.994[/TD]
[TD="align: right"]0.999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD="align: right"]1.7[/TD]
[TD="align: right"]0.183[/TD]
[TD="align: right"]0.493[/TD]
[TD="align: right"]0.757[/TD]
[TD="align: right"]0.907[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.992[/TD]
[TD="align: right"]0.998[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD="align: right"]1.8[/TD]
[TD="align: right"]0.165[/TD]
[TD="align: right"]0.463[/TD]
[TD="align: right"]0.731[/TD]
[TD="align: right"]0.891[/TD]
[TD="align: right"]0.964[/TD]
[TD="align: right"]0.99[/TD]
[TD="align: right"]0.997[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD="align: right"]1.9[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]0.434[/TD]
[TD="align: right"]0.704[/TD]
[TD="align: right"]0.875[/TD]
[TD="align: right"]0.956[/TD]
[TD="align: right"]0.987[/TD]
[TD="align: right"]0.997[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Matty said:
You will need to give us a little more than that to go on...

Rather than specifying how you want to do it, could you describe exactly what you want the formula to deliver?

Matty

Hi Matty The table was no looking good, so I am posting a new table for your reference. The result and queries are in red colour

[TABLE="width: 576"]
<colgroup><col style="width:48pt" span="9" width="64"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[TD="class: xl66, width: 64"]D[/TD]
[TD="class: xl66, width: 64"]E[/TD]
[TD="class: xl66, width: 64"]F[/TD]
[TD="class: xl66, width: 64"]G[/TD]
[TD="class: xl66, width: 64"]H[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl66, width: 64"]14[/TD]
[TD="class: xl66, width: 64"]18[/TD]
[TD="class: xl66, width: 64"]20
[/TD]
[TD="class: xl67, width: 64"]21
[/TD]
[TD="class: xl66, width: 64"]29[/TD]
[TD="class: xl66, width: 64"]31[/TD]
[TD="class: xl66, width: 64"]33[/TD]
[TD="class: xl66, width: 64"]35[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl66, width: 64"]0.363[/TD]
[TD="class: xl66, width: 64"]0.736[/TD]
[TD="class: xl66, width: 64"]0.92[/TD]
[TD="class: xl66, width: 64"]0.981[/TD]
[TD="class: xl66, width: 64"]0.996[/TD]
[TD="class: xl66, width: 64"]0.999[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]3[/TD]
[TD="class: xl66, width: 64"]1.1[/TD]
[TD="class: xl66, width: 64"]0.333[/TD]
[TD="class: xl66, width: 64"]0.699[/TD]
[TD="class: xl66, width: 64"]0.9[/TD]
[TD="class: xl66, width: 64"]0.974[/TD]
[TD="class: xl66, width: 64"]0.995[/TD]
[TD="class: xl66, width: 64"]0.999[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]4[/TD]
[TD="class: xl66, width: 64"]1.2[/TD]
[TD="class: xl66, width: 64"]0.301[/TD]
[TD="class: xl66, width: 64"]0.663[/TD]
[TD="class: xl66, width: 64"]0.879[/TD]
[TD="class: xl66, width: 64"]0.96[/TD]
[TD="class: xl66, width: 64"]0.992[/TD]
[TD="class: xl66, width: 64"]0.999[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]5[/TD]
[TD="class: xl66, width: 64"]1.3[/TD]
[TD="class: xl66, width: 64"]0.273[/TD]
[TD="class: xl66, width: 64"]0.627[/TD]
[TD="class: xl66, width: 64"]0.857[/TD]
[TD="class: xl66, width: 64"]0.957[/TD]
[TD="class: xl66, width: 64"]0.989[/TD]
[TD="class: xl66, width: 64"]0.998[/TD]
[TD="class: xl66, width: 64"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]6[/TD]
[TD="class: xl66, width: 64"]1.4[/TD]
[TD="class: xl66, width: 64"]0.247[/TD]
[TD="class: xl66, width: 64"]0.592[/TD]
[TD="class: xl66, width: 64"]0.833[/TD]
[TD="class: xl66, width: 64"]0.946[/TD]
[TD="class: xl66, width: 64"]0.986[/TD]
[TD="class: xl66, width: 64"]0.997[/TD]
[TD="class: xl66, width: 64"]0.999[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]7[/TD]
[TD="class: xl67, width: 64"]1.5
[/TD]
[TD="class: xl66, width: 64"]0.223[/TD]
[TD="class: xl66, width: 64"]0.558[/TD]
[TD="class: xl67, width: 64"]0.809
[/TD]
[TD="class: xl66, width: 64"]0.934[/TD]
[TD="class: xl66, width: 64"]0.981[/TD]
[TD="class: xl66, width: 64"]0.996[/TD]
[TD="class: xl66, width: 64"]0.999[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]8[/TD]
[TD="class: xl66, width: 64"]1.6[/TD]
[TD="class: xl66, width: 64"]0.202[/TD]
[TD="class: xl66, width: 64"]0.525[/TD]
[TD="class: xl66, width: 64"]0.783[/TD]
[TD="class: xl66, width: 64"]0.921[/TD]
[TD="class: xl66, width: 64"]0.976[/TD]
[TD="class: xl66, width: 64"]0.994[/TD]
[TD="class: xl66, width: 64"]0.999[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]9[/TD]
[TD="class: xl66, width: 64"]1.7[/TD]
[TD="class: xl66, width: 64"]0.183[/TD]
[TD="class: xl66, width: 64"]0.493[/TD]
[TD="class: xl66, width: 64"]0.757[/TD]
[TD="class: xl66, width: 64"]0.907[/TD]
[TD="class: xl66, width: 64"]0.97[/TD]
[TD="class: xl66, width: 64"]0.992[/TD]
[TD="class: xl66, width: 64"]0.998[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]10[/TD]
[TD="class: xl66, width: 64"]1.8[/TD]
[TD="class: xl66, width: 64"]0.165[/TD]
[TD="class: xl66, width: 64"]0.463[/TD]
[TD="class: xl66, width: 64"]0.731[/TD]
[TD="class: xl66, width: 64"]0.891[/TD]
[TD="class: xl66, width: 64"]0.964[/TD]
[TD="class: xl66, width: 64"]0.99[/TD]
[TD="class: xl66, width: 64"]0.997[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]11[/TD]
[TD="class: xl66, width: 64"]1.9[/TD]
[TD="class: xl66, width: 64"]0.16[/TD]
[TD="class: xl66, width: 64"]0.434[/TD]
[TD="class: xl66, width: 64"]0.704[/TD]
[TD="class: xl66, width: 64"]0.875[/TD]
[TD="class: xl66, width: 64"]0.956[/TD]
[TD="class: xl66, width: 64"]0.987[/TD]
[TD="class: xl66, width: 64"]0.997[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Are you saying: Look up 0.80 in the row of 1.5 that is in A2:A11 and return corresonding value of 21 from the first row?
 
Upvote 0
Aladin Akyurek said:
Are you saying: Look up 0.80 in the row of 1.5 that is in A2:A11 and return corresonding value of 21 from the first row?
Click to expand...

Hi, Aladin Akyrek,

Firstly look for a value in column A (say 1.5), then find a nearest value ( say .80) in the corresponding row and return to first cell of of column D where you found nearest value .809
The values 1.5 and .80 are selected from another table for which I want to get a result of 21

I want a common formula to find any value in whole working sheet

Thank You
 
Upvote 0
saji04 said:
Hi, Aladin Akyrek,

Firstly look for a value in column A (say 1.5), then find a nearest value ( say .80) in the corresponding row and return to first cell of of column D where you found nearest value .809
The values 1.5 and .80 are selected from another table for which I want to get a result of 21

I want a common formula to find any value in whole working sheet

Thank You
Click to expand...

You could have just said Yes... I still have an uneasy feeling about the correspondence between the header and the body of the table! That said:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][td]
L​
[/td][/tr]
[tr][td]
1​
[/td][td]
14​
[/td][td]
18​
[/td][td]
20​
[/td][td]
21​
[/td][td]
29​
[/td][td]
31​
[/td][td]
33​
[/td][td]
35​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
2​
[/td][td]
1​
[/td][td]
0.363​
[/td][td]
0.736​
[/td][td]
0.92​
[/td][td]
0.981​
[/td][td]
0.996​
[/td][td]
0.999​
[/td][td]
1​
[/td][td][/td][td]
1.5​
[/td][td]
0.8​
[/td][td]
21​
[/td][/tr]

[tr][td]
3​
[/td][td]
1.1​
[/td][td]
0.333​
[/td][td]
0.699​
[/td][td]
0.9​
[/td][td]
0.974​
[/td][td]
0.995​
[/td][td]
0.999​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
4​
[/td][td]
1.2​
[/td][td]
0.301​
[/td][td]
0.663​
[/td][td]
0.879​
[/td][td]
0.96​
[/td][td]
0.992​
[/td][td]
0.999​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
5​
[/td][td]
1.3​
[/td][td]
0.273​
[/td][td]
0.627​
[/td][td]
0.857​
[/td][td]
0.957​
[/td][td]
0.989​
[/td][td]
0.998​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td]
1.4​
[/td][td]
0.247​
[/td][td]
0.592​
[/td][td]
0.833​
[/td][td]
0.946​
[/td][td]
0.986​
[/td][td]
0.997​
[/td][td]
0.999​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]
1.5​
[/td][td]
0.223​
[/td][td]
0.558​
[/td][td]
0.809​
[/td][td]
0.934​
[/td][td]
0.981​
[/td][td]
0.996​
[/td][td]
0.999​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td]
1.6​
[/td][td]
0.202​
[/td][td]
0.525​
[/td][td]
0.783​
[/td][td]
0.921​
[/td][td]
0.976​
[/td][td]
0.994​
[/td][td]
0.999​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
1.7​
[/td][td]
0.183​
[/td][td]
0.493​
[/td][td]
0.757​
[/td][td]
0.907​
[/td][td]
0.97​
[/td][td]
0.992​
[/td][td]
0.998​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
1.8​
[/td][td]
0.165​
[/td][td]
0.463​
[/td][td]
0.731​
[/td][td]
0.891​
[/td][td]
0.964​
[/td][td]
0.99​
[/td][td]
0.997​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]
1.9​
[/td][td]
0.16​
[/td][td]
0.434​
[/td][td]
0.704​
[/td][td]
0.875​
[/td][td]
0.956​
[/td][td]
0.987​
[/td][td]
0.997​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

L2:
Rich (BB code): 

=INDEX($A$1:$H$1,MATCH(K2,INDEX($A$2:$H$11,MATCH(J2,$A$2:$A$11,1),0),1)+
    (LOOKUP(K2,INDEX($A$2:$H$11,MATCH(J2,$A$2:$A$11,1),0))<K2))
 
Upvote 0
Hi, Aladin Akyrek,

Thank a lot for helping me, code works perfect,
once again thank you

Also I like your caption

Sajith
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Formula to round up to the nearest 10 cents
Replies
5
Views
10K
Mr2017
M
H
  • Question Question
comparing values in a column against multiple sheets and returning subsequent values from other columns
Replies
1
Views
1K
DanteAmor
DanteAmor
D
  • Question Question
Change font color for certain range of cell value using VBA
Replies
8
Views
6K
dave tan
D
C
  • Question Question
Find first match from one range in another range and return related value
Replies
8
Views
1K
Fluff
Fluff
M
  • Question Question
Countif to find Unique value based on another columm
Replies
4
Views
597
Eric W
Eric W

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top