Table cross

[ingdervishi]

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

 

[AlanSidman]

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]

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]

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"))

 

[Peter Bartholomew]

An Excel 365 solution

= LET(
  rowNums, XMATCH(Value, ValueColumn, {-1;1}),
  colNums, XMATCH(Group, GroupHeader)+{0,1},
  INDEX(data, rowNums, colNums))

 

[ingdervishi]

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...

 

[ingdervishi]

An Excel 365 solution

= LET(
  rowNums, XMATCH(Value, ValueColumn, {-1;1}),
  colNums, XMATCH(Group, GroupHeader)+{0,1},
  INDEX(data, rowNums, colNums))

View attachment 75406

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

thanks a lot

 

[ingdervishi]

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

 

[ingdervishi]

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

thanks a lot

 

[Peter Bartholomew]

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.

 

[ingdervishi]

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....

 

[Peter Bartholomew]

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.