Google spreadsheet look up cell based on value of other

Question

The spreadsheet we are trying to use this on and I will be referencing.

I am trying to get cell X4 to display the data from column A relative to the max of column B as displayed in cell X3, ie at the time of writing this the maximum is listed as 27, which was done by "Purelycraft" and so I want it to use formulas to automatically display the name "Purelycraft" as he has the most kills.

Answer 1

=OFFSET($A$1,MATCH(MAX($B$3:$B$22),$B$1:$B$22,0)-1,0)

Breaking it down:

• Use OFFSET to select a cell whose location can be expressed relative to another reference cell.

  OFFSET(cell_reference, offset_rows, offset_columns)
  

  cell_reference: The names in your spreadsheet are in Column A, so we'll use the first cell in the column (A1) as our anchor. To make sure that the reference doesn't get changed if the formula is copied, we'll use an absolute reference ($A$1). (Read more here.)

  offset_rows: We'll calculate how many rows down from A1 it is to the row that contains the maximum value. More on that later.

  offset_columns: The names are all in one column, so 0 is an appropriate value here.

• Use MATCH to find the target row.

  MATCH(search_key, range, search_type)
  

  search_key: This is the value we're looking for. You already had MAX($B$3:$B$22), so we'll keep using that.

  range: This is where we'll look. We'll return to column B for this, but you'll note that we're using the entire column starting at row 1. We could have used the same range that we used for MAX, but if you check the docs for MATCH you'll see that it returns the "relative position of an item in a range that matches a specified value." By starting at row 1, we align with our reference cell A1 in OFFSET, if we just subtract 1.

  search_type: Type 0 finds an exact match.