Using function to referance different cells.

[ChrisLives]

I am trying to use the max function to find the max in a column and then return the value in the column to the left of it. I'm thinking it will involve using max within the lookup function, but can't seem to get it to work. I'm trying to use this in the middle of a macro using VB so if you can help me figure it out that way it would be great.

 

[Mark W.]

If your values are in column B you could use =OFFSET(B1,MATCH(MAX(B:B),B:B,0)-1,-1).