Look up values

[nepotist]

Hello,
I have a spreadsheet with about 10 tabs, each representing data for a unique feature and I have another spreadsheet that I would like to update by looking up data from respective tabs.

Example:
Spreadsheet with 10 tabs has the data in following format

ID From To Data1
13175000 0.000 0.690 a
13175000 0.690 1.344 b
13175000 1.344 2.020 a
13175000 2.020 2.279 c
13175000 2.279 2.300 d
13175000 2.300 2.929 a
13175000 2.929 5.233 b

Spreadsheet 2 where the data needs to be updated

ID From To Data1
13175000 0.000 0.690
13175000 0.690 1.344
13175000 1.344 2.279
13175000 2.279 2.929
13175000 2.929 5.233

I need to retrieve values for column data1 from spreed sheet 1. If you notice the spreadsheet 2 has different from and To Example: in Spread sheet 1 tww data points 2.279 to 2.300 and 2.300 to 2.929 are merged in to one data point in spreadsheet 2. I would Like to either calculate weighted average in this case or take the data from larger point i;e 2.279 to 2.300 has a length of 2.300-2.279 = 0.021 and has value "d" while segment 2.300 to 2.929 (length 2.929-2.300 = 0.629) has value "a", so the value in spreadsheet 2 for data 2.279 to 2.929 should be "a", the value of larger data point

I hope I was able to explain it better.

Any help is appreciated.

[shrivallabha]

Merged data will be painful breed of the lot. However, rest of the data where data is non-merged and as it is can be imported by creating a key on both sheets like:

=CONCATENATE(A2,B2,C2)
And then you can do a simple VLOOKUP but that can not find out the data merged. If the current data (probably part of large data) is something to go by then there are a few cases which will be solved by this.

The rest looks a bit complicated.