Match 2 columns on one sheet to two columns on another sheet and return one value

I have two spreadsheets with basically the same data: Sales Rep, Area Mgr, Account Name, and Item name. However, the data is not in the same place on the spreadsheets, and the second spreadsheet has one piece of information I need added to the first one. I need to match the Account name and item on the first spreadsheet to the Account name and item on the second sheet and return the units sold of the item (found on the second sheet) to a designated column on the first spreadsheet (Actual columns). I have tried nested vlookups and tried match/index but have lost my way! :confused:
spreadsheets attached.

Thanks for any help or guidance!

Kathy

Kathy,

I copied Sheet3 from Spreadsheet2 and pasted it into Spreadsheet1 to make calculations easier.

I added the formula

Formula:

Please Login or Register  to view this content.

in cell E3 on the CPB_HT sheet, and filled that down to E49. I then copied E3:E49 and pasted into column F, G, etc.

I had to change the product text in row 2 of the CPB_HT sheet so that they matched one of the products listed on Sheet3. There were a few products on the CPB_HT sheet that had multiple products (DOS EQUIS, NEWCASTLE, etc.). For DOS EQUIS in cell S2, I added an asterisk to the end (DOS EQUIS*) so that the lookup search would look for all varieties of DOS EQUIS sold to that particular account. If you want to separate the different kinds of DOS EQUIS items, just insert more columns in the CPB_HT sheet and paste the same formulas in those columns. Then you'll just have to change the text in row 2 to match one of the items in the list on Sheet3.

Let me know if this helps.

- Vince

You are an excel genius! Thank you thank you!!

@ ksienerth00

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.