# Find max value in array and return a value in a different array but in the same row

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.

• Now that I asked the question, I'm hoping it's not too confusing... Mar 10, 2019 at 1:36
• can you share a copy/dummy of your sheet? Mar 10, 2019 at 1:46
• @user0 Added it. Mar 10, 2019 at 1:49

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