KentCaudill
Industrial
- Apr 8, 2002
- 24
Lookup Multiple Tables
I use the following formula to look up information in a table on the Table sheet and display the resultant unit weight on the Appurtenance sheet. =IF($C16,OFFSET(PipeField,MATCH($D16,PipeSizes,0),MATCH($E16,PipeSchedule,0))*$G16*$D$3,""
. My problem is there are more tables on the Table sheet. How would I change the formula to pull information from multiple tables on the Table sheet? For instance there are 13 flange tables on the TableSheet. They are named: B16.5WN, B16.5SO, B16.5LJ, B16.5Thread, B16.5Blind, B16.5Socket, B16.5LWN, B16.5VariBody, B16.5Studding, B17.47AWN, B16.47ABlind, B16.47BWN, B16.47BBlind. Typically each table displays the flange size .5 inch through 24 inch diameter in column A3:A23. The flange schedules 150lb, 300lb, 400lb etc. are column labels in row 2. As an example A3:A23 is named B16.5WNSize. Cell A2 is named B16.5WNField. Cells B2:H2 are named B16.5WNSchedule. The cells in range B3:H23 contain the unit weights for each individual flange based on the size and schedule. The other 12 tables are similarly named and may have more or less columns and rows. Regards, Kent.
I use the following formula to look up information in a table on the Table sheet and display the resultant unit weight on the Appurtenance sheet. =IF($C16,OFFSET(PipeField,MATCH($D16,PipeSizes,0),MATCH($E16,PipeSchedule,0))*$G16*$D$3,""
. My problem is there are more tables on the Table sheet. How would I change the formula to pull information from multiple tables on the Table sheet? For instance there are 13 flange tables on the TableSheet. They are named: B16.5WN, B16.5SO, B16.5LJ, B16.5Thread, B16.5Blind, B16.5Socket, B16.5LWN, B16.5VariBody, B16.5Studding, B17.47AWN, B16.47ABlind, B16.47BWN, B16.47BBlind. Typically each table displays the flange size .5 inch through 24 inch diameter in column A3:A23. The flange schedules 150lb, 300lb, 400lb etc. are column labels in row 2. As an example A3:A23 is named B16.5WNSize. Cell A2 is named B16.5WNField. Cells B2:H2 are named B16.5WNSchedule. The cells in range B3:H23 contain the unit weights for each individual flange based on the size and schedule. The other 12 tables are similarly named and may have more or less columns and rows. Regards, Kent.