Excel function help

[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|
+----------------------------------------------------------------+

 

[Harlan Grove]

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

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)