Indexing and matching cells across two sheets

Hi there,

In the example Excel document attached, the Contact Names and Numbers in my address book are on the sheet titled 'Contacts in column A and B respectively.

I need to match up the Contact names in Colomn A to the Numbers Dialled in column D in the sheets October and November.

I have created a column F where this data should dynamically be entered into. Please could someone assist me by defining the formula I should use in these cells?

Thanks in advance for your assistance

Rebecca

One way
In October,
place in F2: =IF(ISNA(MATCH(D2,Contacts!B:B,0)),"Not found",INDEX(Contacts!A:A,MATCH(D2,Contacts!B:B,0)))
Copy down
Do the same in November
-----------------------
Success? Wave it, hit the little star at the bottom left of my responses

Hi Max,

Thanks for your response, it works!

I have realised that the formatting is incorrect on some of the numbers in the call records, as some numbers include the area code (27) before the cell number. Do you have a formula I can use to split out the first two digits on some of the cells that this occurs in? I will then just delete the cells with 27 in them.

Let me know if this is possible.

Thanks in advance,

Rebecca

You could try this in October, November
In G2: =IF(LEFT(D2,2)="27",RIGHT(D2,LEN(D2)-2)+0,D2+0)
Copy down, then copy col G and overwrite col D with a paste special as values. Clear col G

Thank you, it works!

My version of excel does not accept commas but after converting commas to semi-colons it accepted the formulas.

I am trying to calculate the number of minutes spent by adding up the duration figures in column C. The total I am getting is not accurate as the format is in time. What would you recommend in order to calculate total minutes in column c, for both sheets October and November?

Thanks in advance, this has been really helpful.

> .. I am trying to calculate the number of minutes spent by adding up the duration figures in column C

Your data in October (eg) is ideal for pivot table analysis
Create a pivot on the data, drop Number dialled into Row, drop Duration into Data (set it to Sum), format as Time to taste