Excel function help

  • Thread starter Thread starter raja_turbine
  • Start date Start date
R

raja_turbine

Hi I have a table of data in Sheet 1.
I have another table in sheet 2. ( see the attached file).

What I need to do is, I need to get y2 for the data in table 2 from
data in table 1
The rules are, for a given x2. Compare x2 against all x1 in table 1. if
it is found x2=x1 then y2=y1, else, find the next higher available
value of x1( next higher) and y2= y1 ( corresponding to x1 (next
higher)).


+----------------------------------------------------------------+
| Attachment filename: book3.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=376911|
+----------------------------------------------------------------+
 
raja_turbine said:
Hi I have a table of data in Sheet 1.
I have another table in sheet 2. ( see the attached file).

What I need to do is, I need to get y2 for the data in table 2 from
data in table 1
The rules are, for a given x2. Compare x2 against all x1 in table 1. if
it is found x2=x1 then y2=y1, else, find the next higher available
value of x1( next higher) and y2= y1 ( corresponding to x1 (next
higher)).
Click to expand...

Is Table1 sorted on the x1 column? If so, are there any duplicate x1 values?
If not, then MATCH(x2,Table1x1Column) would be the index of the largest
value in Table1's x1 column less than or equal to x2. The next higher x1
would be one more than that index. So,

x2:
=INDEX(Table1,MATCH(x2,Table1x1Column)+(VLOOKUP(x2,Table1x1Column,1)<x2),
Table1x1ColumnIndex)

y2:
=INDEX(Table1,MATCH(x2,Table1x1Column)+(VLOOKUP(x2,Table1x1Column,1)<x2),
Table1y1ColumnIndex)


Otherwise, you'll need brute force.

x2:
=INDEX(Table1,MATCH(MIN(IF(Table1x1Column>=x2,Table1x1Column)),
IF(Table1x1Column>=x2,Table1x1Column),0),Table1x1ColumnIndex)

y2:
=INDEX(Table1,MATCH(MIN(IF(Table1x1Column>=x2,Table1x1Column)),
IF(Table1x1Column>=x2,Table1x1Column),0),Table1y1ColumnIndex)
 
Back
Top