Google spreadsheet searches for cell based on value of others

Question

The table that we are trying to use, and I will refer.

I am trying to get cell X4 to display data from column A relative to the maximum of column B, as shown in cell X3, i.e. at the time of writing this maximum, it is indicated as 27 that “Purelycraft” was made, and therefore I want him to use formulas to automatically display the name “Purelycraft”, since he kills the most.

Answer 1

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

Destruction:

• 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 table are in column A, so we will use the first cell in column (A1) as our anchor. To make sure that the link will not be changed if the formula is copied, we will use the absolute link ($ A $ 1). (Read more here .)

  offset_rows : We will calculate how many lines remain from A1 for the line containing the maximum value. More on this later.

  offset_columns : the names are in the same column, so 0 is a suitable value.

• Use MATCH to find the target line.

   MATCH(search_key, range, search_type) 

  search_key : This is the value we are looking for. You already have MAX($B$3:$B$22) , so we will continue to use this.

  range : Here we look. We will return to column B for this, but you will notice that we use the entire column starting from row 1. We could use the same range that we used for MAX , but if you check the documents for MATCH you will see that it returns "the relative position of the element in the range that corresponds to the specified value." Starting at line 1, we align cell A1 to OFFSET from our link, if we simply subtract 1.

  search_type : Type 0 finds an exact match.