Using function to referance different cells.

ChrisLives · Feb 25th, 2002, 11:59 AM

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. · Feb 25th, 2002, 12:09 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 25th, 2002, 11:59 AM	#1
ChrisLives New Member Join Date: Feb 2002 Location: San Diego Posts: 1	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.

Feb 25th, 2002, 12:09 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	If your values are in column B you could use =OFFSET(B1,MATCH(MAX(B:B),B:B,0)-1,-1).