# Help with extracting data entries from a Cartesian Product table.

#### azreal64

##### New Member
Hello everyone,

I was wondering if I could get some help with an issue I've been stuck on for a while.

Basically, I'm trying to a create a spreadsheet in Excel 2007 that will extract data based on a series of inputs from a Cartesian Product table and interpolate between the points. I can't figure out though how to extract the correct data entries.

My spreadsheet works like this: I have a fixed array (16x4) with columns W, X, Y, and Z. The Z column is the important one since that's what going to be interpolated later. The user is going to input numbers for W, X, and Y and the spreadsheet needs to extract the correct data set from the array.

So for example, if the user inputs 225/31/13, the spreadsheet will find the closest entry that's less than the stated inputs, which would be 200/30/10/7.

This can be done using formulas or VBA, doesn't matter. The ultimate goal is to expand this process to 7 other cases, but that will come later.

I've attached a screenshot of my spreadsheet.
http://i.imgur.com/tvfuAmQ.jpg

Any help is appreciated. Thanks.

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
jargon to me and others I suspect - what is a cartesian product, what are the rules for interpolation - are the 15 rows under the user input row fixed a bit like a lookup table ?

So for example, if the user inputs 225/31/13, the spreadsheet will find the closest entry that's less than the stated inputs, which would be 200/30/10/7.
Why isn't that 7 an 8?

jargon to me and others I suspect - what is a cartesian product, what are the rules for interpolation - are the 15 rows under the user input row fixed a bit like a lookup table ?

Apologies. A Cartesian product (from what I've read) is a table representing all possible combinations of a set of numbers. The interpolation work has yet to be done, but I think I can do it fairly easily. And in the final product, the "Inputs" section and "Interpolation Data" sections will be on a separate page, but for now I figured it's simpler this way.

Why isn't that 7 an 8?
Try and think of it this way. The W/X/Y data are all inputs, the Z data is an output which will come from a computer simulation program, and the spreadsheet's job is only to match the input data, not the output so that the Z data can be interpolated.

So for example, you can see that the W column contains two different inputs, 200 and 250. But what if you wanted to know what Z was with W/X/Y = 205/30/10? What would Z be then?

Does that help clarify at all?

Possibly you are looking for the following.

This array formula in J12 will find the last row where W,X and Y values in column A,B and C are less than or equal to the inputs in G3, H3 and I3, confirm with CTRL-SHIFT-ENTER, not just ENTER:
Code:
=INDEX(\$D\$3:\$D\$18;MAX(IF(((\$A\$3:\$A\$18)<=\$G\$3)*((\$B\$3:\$B\$18)<=\$H\$3)*((\$C\$3:\$C\$18)<=\$I\$3),ROW(\$D\$3:\$D\$18)-ROW(\$D\$3)+1)))
Alternative, if X and Y values will always be less than 1000, array formula confirm with CTRL-SHIFT-ENTER, not just ENTER:
Code:
=INDEX(\$D\$3:\$D\$18,MATCH(1000000*\$G\$3+1000*\$H\$3+\$I\$3,1000000*\$A\$3:\$A\$18+1000*\$B\$3:\$B\$18+\$C\$3:\$C\$18))
To find the W,X and Y values corresponding to the Z value found, enter in G12 and copy to the right to H12 and I12:
Code:
=INDEX(A\$3:A\$18;MATCH(\$J\$12,\$D\$3:\$D\$18))
Otherwise I agree with Rick and would expect 8 in the example.
I guess there is some mistake with duplicate W,X and Y combinations in the data table in the example?

Would this be what you are looking for?

Last edited:
Yes, I just noticed the mistake that I have a few duplicates and that it should be 8, not 7 in my example. Also, MarcelBeug, your 2nd formula works, but the 1st and 3rd ones give me errors.

Apologies, my original formulas are in Dutch and with a ";" as delimiter instead of "," and I see I didn't adjust them all.

Corrected formulas:
Code:
=INDEX(\$D\$3:\$D\$18[COLOR="#FF0000"][B],[/B][/COLOR]MAX(IF(((\$A\$3:\$A\$18)<=\$G\$3)*((\$B\$3:\$B\$18)<=\$H\$3)*((\$C\$3:\$C\$18)<=\$I\$3),ROW(\$D\$3:\$D\$18)-ROW(\$D\$3)+1)))
Code:
=INDEX(A\$3:A\$18[COLOR="#FF0000"][B],[/B][/COLOR]MATCH(\$J\$12,\$D\$3:\$D\$18))

Replies
6
Views
442
Replies
0
Views
306
Replies
3
Views
152
Replies
3
Views
278
Replies
2
Views
253

1,207,423
Messages
6,078,443
Members
446,339
Latest member
keogata

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back