Simplifying as much as possible, my data is structured as below. What I'm trying to return is the sum from one of the component cost columns of table 2, of entries that are "X" category and "TRUE" boolean from table 1. In the example below, the sum for "X" category, TRUE should return $4.00.
Obviously, looking at this simplified example, the solution that immediately jumps to mind is to just join the tables. However, my actual structure for Table 1 has about 30 "Boolean" fields, and the structure for Table 2 has upwards of 100 "Component Cost" fields. I'll be using horizontal lookups (with INDEX/MATCH) to select the desired "Boolean" column and desired "Component Cost" column.
I feel like I'm missing some very obvious solution here, but I've had my head in the weeds with this particular model for about a week and I think I just need some fresh eyes on it.
Table 1
<tbody>
</tbody>Table 2
<tbody>
</tbody>
Obviously, looking at this simplified example, the solution that immediately jumps to mind is to just join the tables. However, my actual structure for Table 1 has about 30 "Boolean" fields, and the structure for Table 2 has upwards of 100 "Component Cost" fields. I'll be using horizontal lookups (with INDEX/MATCH) to select the desired "Boolean" column and desired "Component Cost" column.
I feel like I'm missing some very obvious solution here, but I've had my head in the weeds with this particular model for about a week and I think I just need some fresh eyes on it.
Table 1
| Component Description (text) | Category (text) | Boolean |
|---|---|---|
| A | X | TRUE |
| B | X | text |
| C | X | TRUE |
| D | Y | text |
| E | Y | TRUE |
| F | Z | text |
| G | Z | TRUE |
<tbody>
</tbody>
| Component Description (text) | Component Cost @ Lot Size = 1 | Component Cost @ Lot Size = 2 | Etc... |
|---|---|---|---|
| A | $1.00 | $1.00 | |
| B | $2.00 | $2.00 | |
| C | $3.00 | $3.00 | |
| D | $4.00 | $4.00 | |
| E | $5.00 | $5.00 | |
| F | $6.00 | $6.00 | |
| G | $7.00 | $7.00 |
<tbody>
</tbody>
