Matching multiple criteria in two books, one data is horizontal, the other is vertical.

I have two huge workbooks that I need to compare data and put the answer into one of them. I have created an example workbook with two worksheets. The worksheets represent the two workbooks I'm using.

On the first sheet, named 'workbook one', there is data in vertical format that I need to get onto the second sheet, named 'workbook two', in horizontal format. My actual data is huge - about 20,000 rows and half way through the alphabet on the double-lettered columns. My example workbook is much smaller, but set up the same way. I would like to be able to have a formula that I can drag without changing a ton of info in each cell.

So, from 'workbook one', I want to get the Total Count for a specific Date and BU, to go into the appropriate cell on 'workbook two' with the same BU and Date. For example, the data in 'workbook one' cell D2, would show up in cell C2 in 'workbook two' (and D15 in 'workbook one' would display in D4 'workbook two', etc. )

I don't know where to start. I guess I could do a search on the 'workbook one' A2:D25 to match the BU, once that is matched, match the Date, then see the data in the Total Count column and put that into the appropriate cell on 'workbook two'. Is this possible? My real workbook has 175 (rows)communities that need data in about 150 columns for each community. I really don't want to spend the next five days typing formulas.... please help

I would suggest that you use a helper column on Workbook one, with this formula in E2:

=A2&"_"&TEXT(C2,"mmm yyyy")

Copy this down to the bottom of your data by double-clicking the fill handle (the small black square in the bottom right corner of the cursor, with E2 selected).

Then you can use this formula in C2 of workbook two:

=INDEX('workbook one'!$D:$D,MATCH($A2&"_"&SUBSTITUTE(C$1,"Reviews ",""),'workbook one'!$E:$E,0))

and copy this across and down as far as you need to.

Hope this helps.

Pete

Thank you for your help. This seems like it should work, but I'm getting #n/a in workbook two cell C2 with that formula. any thoughts?

Works okay for me - file attached.

Pete

EDIT: maybe you missed the space after "Reviews "

It works! I had the space behind Reviews, but what I messed up on was this one =A2&"_"&TEXT(C2,"mmm yyyy") I only had two mm.

Thank you!!!!!