=indirect

[Teerings3]

=INDIRECT(ADDRESS(MATCH(A:A,Sheet2!A:A,0)+1,4,4,1, "Sheet2"))

I need help changing this a little, once it finds a match it looks
down 2 rows and over 7 rows for the cell containg the word "Productive"
and then go 1 cell over to the right and that is the value I need.

thanks for the help

 

[Guest]

I think that your Match function is incorrect, the match function requires
the lookup value as the first argument, not a range of cells.

Try match("Productive", Sheet2!A:A,false) and then mess around with the
offsets to get the correct address.

HTH

Martin

 

[Teerings3]

Thanks,
But the first match is employee # and then it needs to look in a range
of cells based on that location to find the word "productive" get that
address and the value I need is one cell over.