array formulas
 

I have a couple of spreadsheets I want to compare and perform a calculation
on depending on certain conditions.

Let's take a one row example:
Say one spreadsheet has this -
from to amount
109 105 -20

and the other has this -
from to amount
109 105 30

I want to generate a new spreadsheet that has this -
from to amount
109 105 50

That is, I want a formula in the third spreadsheet that will look in an
array of all the from and to values in the first spreadsheet...if it finds a
value in the second spreadsheet that is in the array of all the from and to
values in spreadsheet one, I want it to create a value in the new spreadsheet
that will adjust the value in the first spreadsheet to the one in the second
(that is, what value do I have to put in the third spreadsheet that would
adjust the value in spreadsheet one to the value in spreadsheet two. I'm
trying to create a spreadsheet of adjustments. The trick is that I can't do a
vlookup unless I manipulated the first two spreadsheets to concatenate the
from to values (the combination must be unique to compare amount values. If a
combination exists in one spreadsheet but not in the other, I have to create
an adjustment amount value also. Is this doable with array formulas, or do I
have to do a column concatenation on the from and to columns first then a
vlookup?