Google Sheets – How to Get Adjacent Cell of Matched Element

google sheets

I have two columns (A and B) in a Google spreadsheet:

   A              B
Number      Description
   2            desc1
   5            desc2
   1            desc3
   3            desc4

How can I return the content of cell B that's next to the minimum number in column A?

(In this case it's desc3 since 1 is the minimum number in A.)

Best Answer

That is not very accurate example, but it should give you your result and workability.

=indirect("B"&ArrayFormula(sum((row(A2:A5)*(A2:A5=MIN(A2:A5))))))

Explanation: We will use =indirect approach formula.

--- "B" by this we will define what column will be returned --- & this is delimiter that will connect "B" letter of searched column with following returned number of "row"

then goes the ArrayFormula function

--- row(A2:A5)*(A2:A5=MIN(A2:A5) is the range of rows that will be searched for MIN(A2:A5) criteria to return us the needed "row".

Thus we have connected "B" + "3" and got =B3 cell value.

Related Topic