Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lookup table
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. |
#2
|
|||
|
|||
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? -- Regards, Ron |
#3
|
|||
|
|||
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)) "Nathan" wrote: 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. |
#4
|
|||
|
|||
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. "Ron Coderre" wrote: 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? -- Regards, Ron |
#5
|
|||
|
|||
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? -- Regards, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a lookup table with an added varable? | Excel Worksheet Functions | |||
In Excel 2003 how do you get a table to update based on lookup cr. | Excel Worksheet Functions | |||
How do I lookup a table from right to left ? | Excel Worksheet Functions | |||
Index table lookup anomaly | Excel Worksheet Functions | |||
Lookup Table | Excel Worksheet Functions |