CODEWITHSUNDEEP
excelexcel-formulamatchopenoffice.orgopenoffice-calc
Cristiano Santos
Cristiano Santos

Reputation: 2137

Define a name with all row numbers of matching criteria using formula

How can I get a table range with the row numbers of cells that have "Option_A" inside of the table below?

Type
Option_A
Option_A
Option_D
Option_A

In this case, I want to get a defined name that would return the following:

1
2
4

I'm using Openoffice Calc and until now, I tried to define a name as MATCH("Option_A";A2:A9;0) but it did not work because MATCH was only returning the first occurrence.

So, what is the formula that can give me the above result?

EDIT

So, as asked, I have the values on A2:A9 and I want to display the results on C1:C8. Here's what I want from the example above on my worksheet:

    A           B   C
1   Type            1
2   Option_A        2
3   Option_A        4
4   Option_D
5   Option_A

On the left are the row numbers of the sheet and on top are the columns.

Upvotes: 1

Views: 382

Answers (1)

Jerry
Jerry

Reputation: 71558

You can use this formula to get the results you're looking for (it works in MS-Excel-2007 at least):

=IFERROR(SMALL(IF($A$2:$A$5="Option_A",ROW($A$2:$A$5)-1),ROWS($B$1:B1)),"")

Use Ctrl+Shift+Enter to use it, instead of pressing only Enter after entering the formula.

IF($A$2:$A$5="Option_A",ROW($A$2:$A$5)-1) gives the row number of the cells matching Option_A.

SMALL then take the smallest row in the first cell, the second smallest in the second cell, etc.

IFERROR is used to put blanks if ever there are no more Option_A, instead of outputting errors.

Upvotes: 1

Related Questions