Hi experts
I have a set of velocity speeds for soils and rocks and I'm using an index and match formula for these speeds for data I'm importing. The formula is
=INDEX(H28:H45,SUMPRODUCT(--(I28:I45<=D7)*(J28:J45>=D7),ROW(I28:I45))-ROW(28:28)+1)
The reference table H28-J45 is shown below:
<tbody>
</tbody>
The formula works....unless there are overlapping velocities. i.e if I'm returning a speed of 2900 the index & matched rock type could be Limestone, Granite, Basalt and Gneiss resulting in me returning an error. Is it possible for excel to somehow Concatenate these returned values into 1 cell or am I stuck with only being able to return one result?
Thank you for your time!
I have a set of velocity speeds for soils and rocks and I'm using an index and match formula for these speeds for data I'm importing. The formula is
=INDEX(H28:H45,SUMPRODUCT(--(I28:I45<=D7)*(J28:J45>=D7),ROW(I28:I45))-ROW(28:28)+1)
The reference table H28-J45 is shown below:
| Soft CLAY | 40 | 75 |
| Stiff CLAY | 75 | 135 |
| Loose SAND | 135 | 240 |
| Dense SAND & GRAVEL | 240 | 355 |
| Residual Soil | 355 | 600 |
| Metamorphic and Igneous rock | ||
| RQD <50 | ||
| RQD = 65 | 600 | 760 |
| RQD = 80 | 760 | 1500 |
| RQD = 90 | 1500 | 2500 |
| RQD = 100 | 2500 | 3400 |
| Basement | 3400 | 3600 |
| Porous & saturated sandstone | 800 | 1800 |
| Limestone | 2000 | 3300 |
| Chalk | 1100 | 1300 |
| Granite | 2500 | 3300 |
| Basalt | 2800 | 3400 |
| Gneiss | 2700 | 3200 |
<tbody>
</tbody>
The formula works....unless there are overlapping velocities. i.e if I'm returning a speed of 2900 the index & matched rock type could be Limestone, Granite, Basalt and Gneiss resulting in me returning an error. Is it possible for excel to somehow Concatenate these returned values into 1 cell or am I stuck with only being able to return one result?
Thank you for your time!
