0

I'm trying to find a way to get the index of a cell where the value was found; more specifically, I want to return the numerical value of the row where it was found, as I need the number to be used in some calculation.

I tried using "LOOKUP" with the returned MIN or MAX value — but that for some reason returns the no match was found.

Any suggestions? Thank you in advance.

Improve this question
3
  • Welcome to Web Applications Stack Exchange. Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and show your hand-entered expected results there.
    – doubleunary
    Nov 5, 2021 at 10:45
  • The perceived need to find a row number often suggests that you in the end want to locate some value as well, rather than just finding the row number. See What is the XY problem?
    – doubleunary
    Nov 5, 2021 at 10:46
  • @doubleunary The value I want is the value of row where the MIN or MAX values are. The numerical content of the cells is already available through the simple =MIN() and =MAX() formulas.
    – TLSO
    Nov 5, 2021 at 18:41

1 Answer 1

Reset to default
0

Use query(), like this:

=arrayformula( 
  query( 
    split( 
      flatten( row(A2:F) & "→" & A2:F ), 
      "→", false, true 
    ), 
    "select Col1 where Col2 = " & max(A2:F), 0 
  ) 
)
Improve this answer
4
  • That worked! But I would like to know what each component of the formula does. Care to explain? Thank you.
    – TLSO
    Nov 5, 2021 at 18:42
  • See query(), split() and flatten(). An arrayformula() wrapper lets you evaluate a formula over a range of cells rather than a single cell, returning multiple results automatically. It is required here primarily to make the & operator work over arrays.
    – doubleunary
    Nov 5, 2021 at 22:26
  • I assume it makes an array of two columns, one with numbers and one with the values and this syntax tells it somehow to return the number in column 1 when the adjacent cell in column 2 has the result for MAX or MIN? I'm not familiar with these formulas other than ARRAYFORMULA(), and I suppose Google's explanations could be formulated more clearly.
    – TLSO
    Nov 5, 2021 at 22:51
  • That is correct.
    – doubleunary
    Nov 5, 2021 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.