Can something like this be accomplished?
 

I have a workbook with two sheets.

Sheet 2 has data like:
pt R5 R6 R7 R8 R9
010 76.0 58.1 51.3 45.8 37.5
020 81.9 61.9 54.7 49.3 44.1
030 95.0 90.0 87.5 85.0 82.9
040 70.2 57.3 51.0 45.8 41.6
etc....................

Sheet 1 contains for example a pt of 020 and a factor of 60.0. Is there
anyway to pull the R value the would be either , < or = the factor in the
row of 020? Say for example the factor of 60.0 in this case would return a R
value of "<R6".

TIA
--
kr

Can something like this be accomplished?
 

It looks like you're looking for the closest match. If so, assuming
that on Sheet 2, A1:F5 contains the data, and that on Sheet1, A2
contains 020, and B2 contains 60, try...

=INDEX(Sheet2!B1:F1,MATCH(MIN(ABS(INDEX(Sheet2!B2: F5,MATCH(A2,Sheet2!A2:A
5,0),0)-B2)),ABS(INDEX(Sheet2!B2:F5,MATCH(A2,Sheet2!A2:A5, 0),0)-B2),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
kr wrote:

I have a workbook with two sheets.

Sheet 2 has data like:
pt R5 R6 R7 R8 R9
010 76.0 58.1 51.3 45.8 37.5
020 81.9 61.9 54.7 49.3 44.1
030 95.0 90.0 87.5 85.0 82.9
040 70.2 57.3 51.0 45.8 41.6
etc....................

Sheet 1 contains for example a pt of 020 and a factor of 60.0. Is there
anyway to pull the R value the would be either , < or = the factor in the
row of 020? Say for example the factor of 60.0 in this case would return a R
value of "<R6".

TIA

Can something like this be accomplished?
 

Awesome! Thank You!!!!!!!!!!!!!!!!!!
--
kr


"Domenic" wrote:

It looks like you're looking for the closest match. If so, assuming
that on Sheet 2, A1:F5 contains the data, and that on Sheet1, A2
contains 020, and B2 contains 60, try...

=INDEX(Sheet2!B1:F1,MATCH(MIN(ABS(INDEX(Sheet2!B2: F5,MATCH(A2,Sheet2!A2:A
5,0),0)-B2)),ABS(INDEX(Sheet2!B2:F5,MATCH(A2,Sheet2!A2:A5, 0),0)-B2),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
kr wrote:

I have a workbook with two sheets.

Sheet 2 has data like:
pt R5 R6 R7 R8 R9
010 76.0 58.1 51.3 45.8 37.5
020 81.9 61.9 54.7 49.3 44.1
030 95.0 90.0 87.5 85.0 82.9
040 70.2 57.3 51.0 45.8 41.6
etc....................

Sheet 1 contains for example a pt of 020 and a factor of 60.0. Is there
anyway to pull the R value the would be either , < or = the factor in the
row of 020? Say for example the factor of 60.0 in this case would return a R
value of "<R6".

TIA