If then/ Vlookup / Index Match Macro

[MJA001]

Hello,

I'm looking for a magic bullet macro that will match the name of a company in multiple worksheets and display a combined summary cells depending if there was a match or not. In terms of my Excel Workbook, here's what I'm hoping the macro will do. Apologies for the long post, but once I got going on the different variations I couldn't help myself.

If the Company Name in Column A from the "Marketing Leads" worksheet matches the Company Name in the following Worksheets (see Matching rules), then combine the corresponding results in Column B of Worksheet "Marketing Leads".

Matching Rules (these are in order of importance for the summary):
1. If the Company Name matches the name in column B in Worksheet "Priority" , then the result in Worksheet "Marketing Leads" Column C should be: "Priority " (space) name from Column A (comma) in Worksheet "Priority", If no match nothing. Move to Rule 2.
Result Example: Priority Darrell Thompson,

2. If the Company Name matches the name in column C in Worksheet "PCI" then the result in Worksheet "Marketing Leads" Column C should be: Results from Rule 1, AND "PCI " plus the name from Column N in Worksheet "PCI", If no match do nothing. Move to Rule 3.
Result Example 1: Priority Darrell Thompson, PCI John Smith, (the PCI WS could return a different name than the Priority WS)
Result Example 2: PCI John Smith,

3. If the Company Name matches the name in column B in Worksheet "Pipeline" then the result in Worksheet "Marketing Leads" Column C should be: Results from Rule 1 and Rule 2 Plus the name from Column A (space) "Opp " (space) Column C Worksheet (comma space) in Worksheet "Pipeline" , If no match do nothing. Move to Rule 4. *If there are multiple matches, then ONLY the info from Column C should be added to the string*

Result Example 1: Priority Darrell Thompson, PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work, *IT Network Upgrade, *
Result Example 2: Priority Darrell Thompson, PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work,
Result Example 3: Priority Darrell Thompson, Darrell Thompson Opp Q4 Erate and Bond work,
Result Example 4: PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work,
Result Example 5: Darrell Thompson Opp Q4 Erate and Bond work,



4. If the Company Name matches the name in column B in Worksheet "SPA" then the result in Worksheet "Marketing Leads" Column C should be: Results from Rule 1 and Rule 2 and Rule 3 Plus the name from Column A and (space) "SPA # " (space) Column C (space) Column D (comma space) from Worksheet "SPA" , If no match do nothing. **If there are multiple matches, then ONLY the info from Column C and D should be added to the string** Move to Rule 5.

Result Example 1: Priority Darrell Thompson, PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work, *IT Network Upgrade,* Darrell Thompson SPA # 1619H OEM, **19176S Data Center, **
Result Example 2: Priority Darrell Thompson, PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work, Darrell Thompson SPA # 1619H OEM
Result Example 3: Priority Darrell Thompson, Darrell Thompson SPA # 1619H OEM,
Result Example 4: Priority Darrell Thompson, PCI John Smith, Darrell Thompson SPA # 1619H OEM
Result Example 5: PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work, Darrell Thompson SPA # 1619H OEM,
Result Example 6: PCI John Smith, Darrell Thompson SPA # 1619H OEM,
Result Example 7: Darrell Thompson Opp Q4 Erate and Bond work, Darrell Thompson SPA # 1619H OEM
Result Example 8: Darrell Thompson SPA # 1619H OEM,

5. If the Company Name matches the name in column B in Worksheet "CRM" then the result in Worksheet "Marketing Leads" Column C should be: Results from Rule 1 and Rule 2 and Rule 3 and Rule 4 Plus "CRM Claim " the info from Column A (space) Column B (Space) Column C (Space) Column D (comma Space) Worksheet "CRM" , If no match, "Marketing Leads" Column C should display "No CRM Claim by Team", *** If there are multiple matches, then Only dispay columns A B C D in of matching rows in string*** End


Result Example 1: Priority Darrell Thompson, PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work, *IT Network Upgrade,* Darrell Thompson SPA # 1619H OEM, **19176S Data Center, ** CRM Claim Darrell Thompson Baylor University 1400 S 5th St Waco, ***William White Baylor College 1 Baylor Plz Houston, ***
Result Example 2: Priority Darrell Thompson, PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work, Darrell Thompson SPA # 1619H OEM, CRM Claim Darrell Thompson Baylor University 1400 S 5th St Waco,
Result Example 3: Priority Darrell Thompson, PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work, Darrell Thompson SPA # 1619H OEM, No Crm Claim by Team
Result Example 4: Priority Darrell Thompson, PCI John Smith, Darrell Thompson SPA # 1619H OEM, No Crm Claim by Team
Result Example 5: PCI John Smith, Darrell Thompson Opp Q4 Erate and Bond work, Darrell Thompson SPA # 1619H OEM, CRM Claim Darrell Thompson Baylor University 1400 S 5th St Waco,
Result Example 6: PCI John Smith, Darrell Thompson SPA # 1619H OEM, No CRM Claim by Team
Result Example 7: PCI John Smith, CRM Claim Darrell Thompson Baylor University 1400 S 5th St Waco,
Result Example 8: PCI John Smith,No CRM Claim by Team
Result Example 9: Darrell Thompson Opp Q4 Erate and Bond work, Darrell Thompson SPA # 1619H OEM, No CRM Claim by Team Result Example 10: Darrell Thompson Opp Q4 Erate and Bond work, No CRM Claim by Team Result Example 11: Darrell Thompson SPA # 1619H OEM, No CRM Claim by Team Result Example 12: Darrell Thompson Opp Q4 Erate and Bond work, No CRM Claim by Team
Result Example 13: CRM Claim Darrell Thompson Baylor University 1400 S 5th St Waco,
Result Example 14: CRM Claim Darrell Thompson Baylor University 1400 S 5th St Waco, ***William White Baylor College 1 Baylor Plz Houston, ***
Result Example 15: No CRM Claim by Team