LOOKUP & RETURN CELL ADDRESS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
Duke Carey said:
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))
Click to expand...
....

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)))
 
Harlan Grove said:
....

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)))
Click to expand...

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 ?
 
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.
 
Roger Govier said:
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.
Click to expand...

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
 
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.
 
Roger Govier said:
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.
Click to expand...

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
 
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:D 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
 
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
 
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
 
Back
Top