MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Problems with table lookups


Posted by John Stretton on January 21, 2002 10:14 AM

I have a situation where I need to query a set of tables based on 5 different criteria. The data is contained in tables spread over multiple worksheets, multiple tables on those sheets, and multiple row labels.

Example:

A 16 ga steel joist (CSJ) 6"w x 8'l with a deflection of 240 has an allowable load of 291.

The table looks like this:
Worksheet CSJ

6" Joist Depth (assume this is cell a1)
Gauge 20 18 16 14 12
Length Allow
Deflec.
8 L/240 114 156 291 366 502
8 L/360 114 156 240 300 412
8 L/480 110 145 180 225 309
9 L/240 90 123 230 289 397
9 L/360 90 123 168 211 290
9 L/480 78 102 126 158 217
10 L/240 73 100 184 231 317
10 L/360 73 99 123 154 211
10 L/480 57 74 92 115 158
11 L/240 60 82 138 173 238
11 L/360 57 75 92 116 159
11 L/480 42 56 69 87 119

The above table is pretty close to what the spreadsheet looks like. There are separate tables for each joist depth, e.g. 8"w, 10"w. The tables are also split into different worksheets in the same file for different flange depths.

So the first thing the query has to determine is which sheet to scan, then the corresponding size table, then the gauge, length, and deflection from that table to get the desired result.

Is this possible, given the current data structure, or would it have to be modified?


Posted by John Stretton on January 21, 2002 10:17 AM

The table was screwed up when it posted.

Posted by bJ on January 21, 2002 11:25 AM

If I understand you right you might try this....
Lay out your data like this.

Column A B C D E F G H I
Concatenate Inches T2 T3 T4 T5 T6 T7 T8
6"L/240 6" 8 L/240 114 156 291 366 502
6"L/360 6" 8 L/360 114 156 240 300 412
6"L/480 6" 8 L/480 110 145 180 225 309
6"L/240 6" 9 L/240 90 123 230 289 397
6"L/360 6" 9 L/360 90 123 168 211 290
6"L/480 6" 9 L/480 78 102 126 158 217
6"L/240 6" 10 L/240 73 100 184 231 317
6"L/360 6" 10 L/360 73 99 123 154 211
6"L/480 6" 10 L/480 57 74 92 115 158
6"L/240 6" 11 L/240 60 82 138 173 238
6"L/360 6" 11 L/360 57 75 92 116 159
6"L/480 6" 11 L/480 42 56 69 87 119


In Column A, concatenate Inches and T3 so you can get a unique record.
Then run your Vlookup in another sheet to this one. I will name this
sheet 'Test', =vlookup(A2,Test!A:D,4,false).


Posted by John Stretton on January 22, 2002 10:07 AM

I think we are misunderstanding each other. I tried your suggestion, and I was not getting the result that I need. It was giving me a reult of "l240" which is a value that in my query I should already have. The value that I need to find is the allowable load for that deflection value (based on the other values as well.)

I have provided a link to a copy of the tables themselves. Here is what I need to be able to do.

Take a look at the worksheet labeled sheet1. I need to fill cell K2 with a value based on the other values in Row 2. The coorect value for the current set is 90, which is found in sheet "CSJ" at location U28. What I need to know is if I can get there from here. If I cannot, what do I need to do so that I can.

The tables were originally designed with printing in mind. There are 4 other spreadsheets much like this one, containg data that I would like to be able to query in the same manner. This set of tables is actually the least complicated of the five, and should be the easiest to start with.

: The table was screwed up when it posted.