Macro for looped VLOOKUP and IF Formula

[DancingElvisLives]

Again, thanks in advance to all those taking the time out to read this thread and support me in resolving my problem. Muchos Appreciados.

Please refer to the attachment in the first instance.

What I need is a macro to loop down the list of names in sheet 1 and when it reaches the last line for a specific name - insert a VLOOKUP and IF formula to retrieve data from sheet 2 and test the value against info in another cell.

If I use the first agent Ibrahim Abdiselam as an example. I want the macro to search down the list until it gets to the last entry for Ibrahim, which in this case is row 6, then in H6 enter a VLOOKUP formula which looks up the value in A6 (the agents name) in Sheet 2 columns A:B and returns the value in column B. Once finished, I need the macro to continue to the next name and repeat, all the way to the bottom of the list.

Once thats done - I then need, possibly an IF statement to be entered in the adjacent cell to the cell that has the VLOOKUP formula. So for example in Ibrahims case in I6, and calculate whether the time D6 is within 3mins either side of the time returned in H6 amd if so return a positive result. So for example, in Ibrahims case, as 1609 is within 3 mins of 1610 then it would return a positive result. Alternatively, if the time in D6 was 1612, then it would still return a positive result, because it's within the 3min buffer. Again I need this to repeat for the last entry of every agent all the way to the end.

Hope you can help.

Tidy Butt

[Richard Buttrey]

Hi,

You don't need a macro

H1
=IF(A1=A2,"",VLOOKUP(A1,Sheet2!$A$1:$B$4,2))

I1
=IF(H1<>"",IF(ABS((H1-D1)*24*60)<=3,"Yes","No"),"")

and copied down.

Regards