Append data with a highest value from a range value

I have the following data set with a given value for Range1 and Range2.

# Range1 Range2 val1 val2 val3 val4
1 101 110
2 111 120
3 121 130

The input file will contain only the following data

Range val1 val2 val3 val4
111 0 0 5 0
129 0 0 0 1
112 0 0 7 2
105 1 2 3 4
109 5 5 2 1
122 1 0 5 0

Based on the input file above it should check the range value and append the file IF and only IF if is greater than the existing value. In other words only the highest value will be added within the rage. The above 2 tables should give the following result

# Range1 Range2 val1 val2 val3 val4
1 101 110 5 5 3 4
2 111 120 0 0 7 2
3 121 130 1 0 5 1

I hope I am making myself clear here. Hope to hear from you soon.

Matt

In validating numbers you may use the following condition "Is Number Between" and "Is Number More Than" in the properties.

In addition, we can use Range Lookup transformation function, this should be more appropriate than the previous answer.