Table cross

ingdervishi

New Member
Hi all, Can any one help with the attached table?
the topic is to find automatically the numbers, in these case the number the bold and red.

let me explain what i need: for example i have a given value "22", and we get answer from the row above and row below, and then from the selected columns (named Group 1,2,3 ...n), in this case Group 2. the furor numbers that came as this result (matrix, crossing two rows with two columns) is what is need.

i don't know if i could explain exactly what i wont or not, but if any one can help me i will be very
thank fully

best regards

Attachments

• 10.3 KB Views: 9
Last edited by a moderator:

AlanSidman

Well-Known Member
How do you know to look in group 2. No indication in your workbook that you are looking for 22 either. What would your answer be if you were looking for 18? We need more information on how you determine criteria. Use your example to explain.

ingdervishi

New Member
First of all thanks for your time and answrt also…

if the value should be 18 (in the first column we will take the row when is “10” and the row when is “20”.

in my mind i had to put somewhere : choose group and define value, and than to get data automatically.
The table maybe we can arrange in any other way…
What ai want is how to get these numbers by formula (bot manyually) becouse these numbers i use for other formula

thanks again

bosco_yip

Excel Ninja
Maybe,

1] Enter Criteria in cell L8

2] In "Lower bound" K11, formula copied right to L11

=INDEX(\$D\$11:\$I\$15,AGGREGATE(14,6,ROW(\$C\$11:\$C\$15)-ROW(\$C\$10)/(\$C\$11:\$C\$15<=\$L\$8),1),MATCH("Group "&LEFT(\$L\$8),\$D\$8:\$I\$8,0)+(K\$9="To"))

3] In "Upper bound" K12, formula copied right to L12

=INDEX(\$D\$11:\$I\$15,AGGREGATE(15,6,ROW(\$C\$11:\$C\$15)-ROW(\$C\$10)/(\$C\$11:\$C\$15>=\$L\$8),1),MATCH("Group "&LEFT(\$L\$8),\$D\$8:\$I\$8,0)+(K\$9="To"))

Attachments

• 13.5 KB Views: 3

Peter Bartholomew

Well-Known Member
An Excel 365 solution
Code:
``````= LET(
rowNums, XMATCH(Value, ValueColumn, {-1;1}),
INDEX(data, rowNums, colNums))``````

ingdervishi

New Member
Maybe,

1] Enter Criteria in cell L8

2] In "Lower bound" K11, formula copied right to L11

=INDEX(\$D\$11:\$I\$15,AGGREGATE(14,6,ROW(\$C\$11:\$C\$15)-ROW(\$C\$10)/(\$C\$11:\$C\$15<=\$L\$8),1),MATCH("Group "&LEFT(\$L\$8),\$D\$8:\$I\$8,0)+(K\$9="To"))

3] In "Upper bound" K12, formula copied right to L12

=INDEX(\$D\$11:\$I\$15,AGGREGATE(15,6,ROW(\$C\$11:\$C\$15)-ROW(\$C\$10)/(\$C\$11:\$C\$15>=\$L\$8),1),MATCH("Group "&LEFT(\$L\$8),\$D\$8:\$I\$8,0)+(K\$9="To"))

View attachment 75398
thanks a lot... almost you have done it (but maybe i didn't explain well). please see again the attached file...

Attachments

• 15 KB Views: 0

ingdervishi

New Member
An Excel 365 solution
Code:
``````= LET(
rowNums, XMATCH(Value, ValueColumn, {-1;1}),
INDEX(data, rowNums, colNums))``````
View attachment 75406
please can you give me the excel file with code integrated?

thanks a lot

ingdervishi

New Member
thanks a lot... almost you have done it (but maybe i didn't explain well). please see again the attached file...

Attachments

• 11.2 KB Views: 2

ingdervishi

New Member
please can you give me the excel file with code integrated?

thanks a lot

Attachments

• 11.2 KB Views: 2

Peter Bartholomew

Well-Known Member
The attached shows the 2D lookup. It then goes on with the bi-linear interpolation but that is not well supported by Excel functions. Interpolating for the Max and Min in one formula is not that easy.

Attachments

• 15.3 KB Views: 2

ingdervishi

New Member
The attached shows the 2D lookup. It then goes on with the bi-linear interpolation but that is not well supported by Excel functions. Interpolating for the Max and Min in one formula is not that easy.
thanks a lot for your time...in the table attached i made a Simpy table, please can you check this one.....

best regards sincerely....

Attachments

• 11.4 KB Views: 1

Peter Bartholomew

Well-Known Member
There isn't really a right or wrong way of going about this. There are a number of approaches possible, each with strengths and weaknesses.

Attachments

• 16.1 KB Views: 2