lookup table

N

Nathan

I have a table setup much like this.

TW Sq Ft
0-255
1 .743
2 .745
3 .746
4 .748

If I enter a value of 245 and 4 I want it to return .748 because it is
between 0 and 255 and a TW of 4. Im lost.
 
R

Ron Coderre

First, enter these numbers in Cells A1:A5
0
0.743
0.745
0.746
0.748

B1: SqFt Amount (example: 245)
B2: TW value (example: 3)
B3: =OFFSET(A1,(B1>=0)*(B1<=255)*B2,0)
(in this example: B3 will equate to 0.746)

Does that give you something to work with?
 
B

bj

set up your area row with just the maximum area
if say you had
TW column in column A

lables in row 3
max areas in row 4 out to column L
Twist of interest in A1
Area of interest in B1
Data to row 10
enter in C1
=vlookup(A1,A3:L10,match(B1,A2:L2))
 
N

Nathan

Its the right concept but I should have been more detailed.

The TW numbers are actually in this format

TW 0-249.9 255-461
40 .743 .752
40.5 .752 .761
41 .761 .770

So if I have 256 and 40.5 it would return .761. If I have 220 and 40 it
would return.743.
 
R

Ron Coderre

Ok...Let's try this:

Data table in Cells A1:E8
0 250 255 461.1
40.0 0.743 0 0.752 0
40.5 0.752 0 0.761 0
41.0 0.761 0 0.770 0
41.5 0.770 0 0.779 0
42.0 0.779 0 0.788 0
42.5 0.788 0 0.797 0
43.0 0.797 0 0.000 0

G1: 250 (sq ft value)
G2: 40 (TW value)
G3: =OFFSET($A$1,MATCH($G$2,$A$1:$A$8,0),MATCH($G$1,$B$1:$E$1,1))

Note: I have SqFt as an approximate match and TW as an exact match, as
indicated by the 0 and 1 in the MATCH formulas,respectively.
Also, I gathered from your data that no SqFt values from 250-255 are covered
by the table, so I defaulted their calculated results to zero. Same for
Values over 261.

Am I helping yet?
 
Top