Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need Help with lookup values in Table
I have a table Like This:
Tube Diameter 15 14 13 12 11 10 9 15,000 60 54 50 45 40 36 32 12,000 45 42 39 35 32 29 26 9,000 33 30 29 26 24 21 18 7,500 26 24 22 21 19 17 15 6,000 20 19 18 16 15 13 12 5,000 17 16 15 12 11 10 8 4,000 13 12 11 10 9 8 7 3,000 10 9 9 8 7 6 5 2,000 7 6 6 5 5 4 4 Transfomer Size The table data represents the Maximum footage (of Neon Tubing) allowed for a particular transformer size based on the Tube Diameter. The information that I want to input is: 1. Tube Diameter 2. Actual Footage I need to write a Lookup function to do several things. The first is, for a particular job I will not always have the exact footage listed in the chart, so I will need to round the actual value up to the nearest value based on the tube diameter. I have done this with the HLOOKUP function but I have to manually put in the Row Index, and is therefore not practical. Second, I need to Reference or Pick the correct size transformer based on this rounded-up value and the tube diameter. I would also like a way to divide the footage if it fell out of the scope of the chart, i.e. if there was 85 feet determine that I need a 15,000 and a 7,500 transformer. I can get the Column index with a MATCH Function based on the Tube Size but I can't seem to get the Row index unles I only search in one column. Let me know if anyone has some ideas. Thanks, cank |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique values - Pivot Table | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Charts and Charting in Excel | |||
Pivot table page filter not accepting multiple values. Workaround? | Excel Worksheet Functions | |||
How do I increase a table values by 1.2 for example? | Excel Worksheet Functions | |||
how can i fill a table with values from repeated regressions | Excel Worksheet Functions |