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.

Attached Files

samplecalldata.xlsx (38.4 KB, 17 views)

For anyone interested, the following formula worked after I figure out how to do it. ((Make sure you make this an array)) F2, CTRL-SHIFT-ENTER rather than just enter after inputting formula.

=INDEX(CallData!$C$2:$C$9,MATCH(1,
IF(ISNUMBER(MATCH(CallData!$B$2:$B$9,$B2:$D2,0)),
IF(CallData!$C$2:$C$9>=$E2,1)),0))