Index & match overlapping data

[mrsak87]

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:

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
RQDa 0 500
RQDb 600 760
RQDc 760 1500
RQDd 1500 2500
RQDe 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

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!

[FDibbins]

You are not actually using MATCH(), you are using SP, but thats not really the point here.

INDEX (like vlookup) generally returns the 1st match it finds, then stops looking. I think, for what you want, you need to use something like index/small/if and then concat them

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[mrsak87]

Hey,

Thank you for your reply. I've attached a sample workbook. I'm still having trouble - it took me about an hour to work out the index andsumproduct formula!

[FDibbins]

I got it using a helper (D), with this copied down...
=IF(AND($B$9>=B2,$B$9<=C2),A2,"")

Then B9=SUBSTITUTE(TRIM(D2&" "&D3&" "&D4&" "&D5&" "&D6&" "&D7)," ",",")

[mrsak87]

Cheers for the help, I ended up going with your small method

{=INDEX($N$14:$P$37,SMALL(IF(($O$14:$O$37<=$D$7)*($P$14:$P$37>=$D$7),ROW($O$14:$O$37)-13),ROW(C1)),1)}

D refers to the velocity
N,O and P refer to rock type, lower velocity & higher velocity respectively

Cheers!

[FDibbins]

good stuff, thanks for the feedback