• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

75395

best regards
 

Attachments

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


75398
 

Attachments

Peter Bartholomew

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

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

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

Top