Offset in combination with match and Vlookup

Hello all,

I'm trying to find out how to use offset in combination with match and vlookup. Well I think I have to use Offset to find the value ( cell with time in it).

I have in my workbook 3 sheets: Sheet1, Sheet2 and Agents.

In 'Sheet2' every week I upload a report with persons and every person has a certain amount of time behind their name.
In 'Sheet1' I want to get (load) the data: the person and time from 'Sheet2'.
In 'Agents' I only match the names. That's because the names in the report I upload in 'Sheet2' have a different lay-out then the ones I use.

The matching and to get the names correct in 'Sheet1' Is no problem. Though I get stuck with the cells where the time is placed in the report I upload in 'Sheet2'.
The persons are in Column C ( C7, C26, C45, C64 etc) but the value I also need to get is not in line behind the names. It's In the 7th row under the name and in Column L.

Example:


Wiebe (C7) time ( L14)
Gary (C26 time ( L33)
Kay (C45) time ( L52)

What I use to match the names and get data is this formula.

=INDEX(Sheet2!$A:$L;MATCH(VLOOKUP(Sheet1!$A2;Agents!$A:$E;5;0);Sheet2!$C:$C;0);MATCH(B$1;Sheet2!$1:$1;0))

Is it possible to use Offset ( or something different) in this formula to also get the cells with the time ( matching with the right person)

I suppose yo want to count the time of each member.

Maybe it also can be done, with an pivot table on sheet 2.

I think you get better help if you add an excel file, without confidential information on the forum.

Also add the desired result in the workbook.

Another approach (VBA)

run this macro and you get the rows you need.

The code needs to be cleaned up (but it gives you the idea).

Please Login or Register  to view this content.

Hello Wiebie you could also try
=INDEX(Sheet1!L:L,MATCH("*"&LEFT($A2,FIND(" ",$A2)-1),Sheet1!C:C,0)+7)

try this one
=INDEX(Sheet1!$L:$L,MATCH(VLOOKUP(Sheet2!$A2,Agents!$A:$E,5,0),Sheet1!$C:$C,0)+7)

you could also use
=INDEX(Sheet1!$A:$Z,MATCH(VLOOKUP(Sheet2!$A2,Agents!$A:$E,5,0),Sheet1!$C:$C,0)+7,MATCH(Sheet2!B$1,Sheet1!$1:$1,0)-2)
-2 because your result is 2 columns to the left of match found.

You are welcome and thanks for the feedback.