Excelent,

Could you explain a little more on how to use this. Its a little over my
experience level, but I'd love to know how to do this. So far I have
created a new module and pasted you code. I then changed each Sheet1! to
Losses! (the name of the worksheet with the data). This is the only change
I made on your code. I get an error saying Compile Error: Expected: line
number or label or statement or end of statement. Was there something else
needing changes?

Thanks
Dan



try this function. but it depend on that ur custumernames are in column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.

put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function