criteria based data move data between spreadsheets using SUMIF

[GStephen]

example spreadsheet is attached.
Data in col A and col b are concatenated in col AL in both MstrRollUp and data2 tab (done to create a more complex search criteria). What I am trying to do, and was able to do in older versions of Excel, is use SUMIF 'Range' and 'Criteria' arguements to find a match between spreadsheets and then use the 'SUM Range' arguement to move data between the two spreadsheets. In the attached example, based on positive search result, I want to move the contents of tab 'data2', col Z to tab 'MstrRollUp', col AC. I have attached a 10 row data sample. In reality, the data exists in two different workbooks of differing size and I wnat to move 5800 'hits' out of a 28000 row workbook.

I have used SUMIF this way before. I found that SUMIF did not care if the data was alpha or numeric. If the receiving cell was empty the result of the 'SUM Range' arguement populated the receiving cell with the data found in relation to the positive match. In 2010 the receiving cell now contains, '0'.

I am very coachable and proof beyond any doubt that you can teach an old dog new tricks.

[L-Drr]

I think this is the formula you are looking for (in 1st tab cell AC2):

=LOOKUP(AO2,data2!AL$2:AL$10,data2!Z$2:Z$10)


Edit: I see now that this returns 2 errors, but I cannot figure out for the life of it where they are coming from...