OFFSET AND Address of Minimum Value does not work

dwg83

Board Regular
Joined
Nov 8, 2006
Messages
174
1 2 3 4

45 55 30 60

Here is my issue, I want to find the minimum value of the second line, but return the value located two rows above it....so 3

I thought this formula was going to work but no such luck

=OFFSET(CELL("address",INDEX(C1:C4,MATCH(MIN(C1:C4),C1:C4,0))),-2,0)

Help, Thanks!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,882
Office Version
  1. 2019
Platform
  1. Windows
For reference, CELL("Address",???) returns the address as a text string, so you would need to use INDIRECT(CELL("Address",???)) to return a valid range. The same applies to the ADDRESS(row,column) function, and possibly others that don't spring to mind.

As peter's suggestion shows, there is (usually) a way to achieve the same result without the need to nest several functions.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,485
Messages
5,529,143
Members
409,851
Latest member
Ingar
Top