Lookup in 3D space but against ranges of values

[CivilPengineer]

My problem relates to a computer model of a building.

I have thousands of nodes output from the model each of which has stress values associated with it. Each nodes has an X, Y, Z value describing it's location in 3D space.

The strength properties of the building are assigned in hundreds of rows. Each row corresponds to a range of X, Y and Z values. For example, x between 0 and 50, y between <x<50, 1<y<13,="" -12<z<-10="" might="" describe="" a="" wall="" with="" certain="" strength,="" whereas="" 0<x<50,="" 13<y<20,="" the="" above="" which="" is="" weaker.
0 and 5 and z between 4 and 5 could describe a wall in the building with a certain strength property. x between 0 and 50, y between 5 and 10 and z between 4 and 5 could describe the wall above which would have a different strength value.

What I am trying to do is, for each node, find the associated strength value at that location, by automatically finding which set of ranges the node X,Y,Z values fall into and copying the associated strength properties to that node's row.

I'm hoping to do this without using macros, as it is easier for my work to be checked if I can stick to formulae.

Any ideas would be much appreciated!</x<50,>

 

[Gerald Higgins]

Hi, sorry I haven't posted back for a few days, been busy elsewhere.

I've been working on this, and I'm getting close, but haven't got the full solution yet.

I'm convinced that you'll have to sort your data I'm afraid, and if the relevant cells are sometimes merged, you'll have to undo that too I think.

In the back of my mind, I keep thinking there must be an easier way to do this, but I haven't been able to work it out yet.

ANYWAY, here's what I have so far.

I've assumed that you sort the data using X Min as the first sort key, then Y min as the second sort key, and then Z Min as the third sort key.
If you do this, the Max data is redundant (I think).

This formula will lookup the correct code using the X and Y values only. It doesn't take account of the Z values, and so just returns the code for the first correct match of X and Y.

=INDEX(K2:K28,MATCH(LOOKUP(A2,E2:E28),E2:E28,0)
+MATCH(LOOKUP(B2,OFFSET(G2,MATCH(LOOKUP(A2,E2:E28),E2:E28,0),0,1000,1)),
OFFSET(G2,MATCH(LOOKUP(A2,E2:E28),E2:E28,0),0,1000,1),0))

I've been trying to extend this logic to the Z column, and what I've got so far works correctly sometimes but NOT ALWAYS.

Anyway can you test this formula please, let me know if it works for you, for X and Y, and I'll try and extend the logic to the Z data.

 

[CivilPengineer]

Hi Gerald
Thanks for getting back to me. Yes, I have sorted my real data (for my sample calculation at least) by x-min, then y-min, then z-min.

I tried your formula on my simple values, unfortunately it doesn't seem to work. I'm not sure how it works so I couldn't point to where the problem is.

However, I have come up with another method which seems to work for me, for both the "simple" values and the "real" data. See pic below:

I have introduced three columns: "x-least", "y-least" and "z-least". For each node, these show the corresponding lower end of the range into which that node falls. The formulae for these are as follows:

In cell D3:

{=INDEX(I$3:I$12,MATCH(A3,I$3:I$12,1))}

- (array formulae with CTRL-SHIFT-ENTER) - this uses the match type 1 to find the value in cell A3 (the X value) or the next lowest.

In cell E3:

{=INDEX(K$3:K$12,MATCH(B3,IF(I$3:I$12=D3,K$3:K$12),1))}

- (array formulae with CTRL-SHIFT-ENTER) - this uses an IF statement so that when we are matching the Y value in cell B3 against the Y-min values in the K column, it reduces the range that the MATCH function looks in by limiting it to those cells where the X-min value (in I column) matches the x-least (D3) value found previously.

In cell F3:

{=INDEX(M$3:M$12,MATCH(C3,IF(IF(I$3:I$12=D3,K$3:K$12)=E3,M$3:M$12),1))}

- (again, array formulae with CTRL-SHIFT-ENTER) - this extends the same principle with a nested "IF" so that it looks for the Z value within a doubly reduced selection in column M - i.e. only those values where the X-least/x-min values match and the y-least/y-min values match.

Then, with the x-least, y-least and z-least values, it's a case of doing a three-way lookup, which seeing as the values will match precisely, can be done with match type 0 and does not require the ascending order. So Cell G3 has the following code:

{=INDEX($O$3:$O$12,MATCH(D3&E3&F3,$I$3:$I$12&$K$3:$K$12&M$3:M$12,0))}

- again, entered as an array function.

This seems to work. There are some #N/A errors in the picture, that's because I have some nodes where the co-ordinates do not match any of the lookup data ranges - in this case, there is no corresponding z range. I'm glad it does this actually, it will help me to spot if there are any rogue nodes in my data.

If you can spot any weaknesses in what I have done I would appreciate it. I was thinking I should maybe do a max check as currently entering a node with x=100,y=100,z=100 just returns placeholderJ. But I think I can probably do this just by writing a simple little check cell off to the side which looks at the max node co-ordinates of X, Y and Z and checks them against the max values of x-max, y-max, z-max.

Thanks again for the input.

Luke