Vlookup or Index/Match - Multiple Criteria
I have the following data in my excel workbook.
I am trying to match customer data to call data. Below find a simplified explanation of how the workbook is laid out.
Worksheet 1 (customer data):
(A) - Customer Unique Identifier
(B) - Phone 1
(C) - Phone 2
(D) - Phone 3
(E) - RequestDate
Worksheet 2 (call data)
(A) - Call unique Identifier (different than customer identifier above)
(B) - Phone Number Dialed
(C) - Connected Date Time
I am trying to create a lookup that will perform the following:
For each item in Worksheet1 (A), match Worksheet1 (B), (C), or (D) to Worksheet 2 (B) and bring back the first instance of (C) that is equal to or greater than Worksheet1 (E) back to worksheet 1.
At first I tried a vlookup but I only based it on 1 phone number in Worksheet 1 and Worksheet2 (C) at times was before the date in worksheet1.
Phonenumber is the only possible way to match data between two the worksheets.
Attached is a very basic workbook with sample data and logic.
Please let me know the best way to approach this.
|