LOOKUP & RETURN CELL ADDRESS

[Carolan]

What formula would I use to look up the address of the maximum value in a
column and return the cell address not the value?

 

[Aladin Akyurek]

What formula would I use to look up the address of the maximum value in a
column and return the cell address not the value?

See the formula system I describe in:

http://tinyurl.com/dph4d

 

[Duke Carey]

Assume you have a column header in row one, & that cell is named 'hdr'
Assume your column of values is named tbl, and starts in row 2

=ADDRESS(ROW(OFFSET(hdr,MATCH(MAX(tbl),tbl),0)),COLUMN(hdr))



will give you the address of the FIRST value equal to the max value in the
list

 

[Harlan Grove]

Assume you have a column header in row one, & that cell is named 'hdr'
Assume your column of values is named tbl, and starts in row 2

=ADDRESS(ROW(OFFSET(hdr,MATCH(MAX(tbl),tbl),0)),COLUMN(hdr))

....

MATCH(MAX(tbl),tbl) would only work when tbl is sorted in ascending order,
in which case ROWS(tbl) would be much simpler. Perhaps you meant
MATCH(MAX(tbl),tbl,0)?

More compact to use

=CELL("Address",INDEX(tbl,MATCH(MAX(tbl),tbl,0)))

 

[YellowTump]

....

MATCH(MAX(tbl),tbl) would only work when tbl is sorted in ascending order,
in which case ROWS(tbl) would be much simpler. Perhaps you meant
MATCH(MAX(tbl),tbl,0)?

More compact to use

=CELL("Address",INDEX(tbl,MATCH(MAX(tbl),tbl,0)))

This works brilliantly, and by substituting 'Row' for 'Address' I can get
the row number. Now I want to refer to another cell in that same row, and
extract the value from it. I tried:

=C(cell("row",index(tbl(match(max(tbl),tbl,0))))

where 'C' is the column from which I want the value, but it doesn't work.

Background: My worksheet has twelve month columns, and a total column. Rows
are years, 'tbl' is the total column. The formula (as amended) gives me the
row containing the highest annual total, and I want to refer to the
individual months in that year.

Any ideas, please ?

 

[Roger Govier]

Hi

Create a named range to cover all of your data, called myData
Then use
=INDEX(myData,MATCH(MAX(tbl),tbl,0),COLUMN(B1))

to pick up the value for January, assuming that column A holds the Year
number and column B holds January data
As you copy across, column(B1) will change to C1, D1 etc to give you values
for Feb, Mar etc.

 

[YellowTump]

Hi

Create a named range to cover all of your data, called myData
Then use
=INDEX(myData,MATCH(MAX(tbl),tbl,0),COLUMN(B1))

to pick up the value for January, assuming that column A holds the Year
number and column B holds January data
As you copy across, column(B1) will change to C1, D1 etc to give you values
for Feb, Mar etc.

Thank you, that was quick !

It works, but it appears that the 'column(B1)' is referring to a sort of
sub-range of columns within 'myData', not to the spreadsheet column of
similar name. Is that right ?

Regards,

Tim Dawson

 

[Roger Govier]

Hi Tim

COLUMN() returns the column number, 1 for column A, 2 for B etc.

Specifying COLUMN(B1) is telling the formula to use 2 as the column Offset
in the Index of myData.
As you drag across, it steps the number up accordingly.

It is exactly the same as typing
=INDEX(myData,MATCH(MAX(tbl),tbl,0),2)
but it saves having to alter the column offset manually as you copy across
the page.

 

[YellowTump]

Hi Tim

COLUMN() returns the column number, 1 for column A, 2 for B etc.

Specifying COLUMN(B1) is telling the formula to use 2 as the column Offset
in the Index of myData.
As you drag across, it steps the number up accordingly.

It is exactly the same as typing
=INDEX(myData,MATCH(MAX(tbl),tbl,0),2)
but it saves having to alter the column offset manually as you copy across
the page.

Hello Rogar,

Thanks, I thought it was something like that. I've been able to copy it
right across as you say.

I see the significance of the 'B' (B=2), but changing the '1' in B1 to any
other row number (e.g. 'B11') makes no difference to the result, so I don't
understand what the '1' is for.

It's not important in the great scheme of things, but it helps to know what
I'm doing, in case I need to do it again !

Regards,

Tim Dawson

 

[Roger Govier]

Hi Tim

As you say the row number has no significance in this equation, as we are
using the COLUMN() calculation, but we are giving the column function a cell
address from which to calculate the column number.
=COLUMN(B) on its own, will not work
=COLUMN(B:B) will give exactly the same result as COLUMN(B1)

=COLUMNS($A:B) will also give a result of 2, counting from the fixed $A to
B, and will extend to $A:C, $A as we copy across to give 3, 4 etc.
note the plural in the above, as this is a count of the columns within the
specified range.

My preference has always been to give the reference of row 1 for whichever
column number I want the numeric value from, hence B1

=ROW(A1) will be 1, regardless of the column letter
=ROW(C13) will be 13

 

[YellowTump]

Hello Roger,

Thank you for that full explanation. It's been a long time since I needed
to learn about an Excel function that was new to me (although I'm sure there
are lots more I know nothing about, but haven't yet needed).

It's great to find such quick and authoritative help. Thanks again.

Regards,

Tim

 

[YellowTump]

Hello Roger,

Thank you for that full explanation. It's a long time since I needed to
learn about an Excel function I hadn't met before, though I'm sure there are
plenty more out there that I haven't needed yet.

It's great to know there's help out there when one needs it. Thanks again.

Regards,

Tim Dawson

 

[David Biddulph]

When you need to learn about an Excel function that is new to you, try Excel
help.