Google Sheets – Find Max Value in Array and Return Value in Same Row

formulasgoogle sheets

I have four columns, the two in the middle only being there to keep track of other values, not relevant to this.

I'm trying to

  1. Search for the highest value in, say, D3:D999
  2. Get the row of that highest value
  3. Return the content of the cell A(row), with row being the same as found earlier

I already tried =VLOOKUP like this.

=VLOOKUP(MAX(D3:D999),A3:D999,1,0)

It works until there is no value in D3:D999 equal to a value in value in A3:A999, at which point I get an #N/A.

Additionally, I tried this:

=VLOOKUP(MAX(D3:D999),A3:D999,1,1)

It just showed the highest value it could find in A3:A999, so no solution either.

EDIT 1: Link to an editable copy of the spreadsheet: here

EDIT 2: I'll leave this spreadsheet accessible if someone else needs it.

Best Answer

=OFFSET(INDIRECT(CELL("address", INDEX(D3:D, MATCH(MAX(D3:D), D3:D, 0), 1))), 0, -3)

5