CODEWITHSUNDEEP
excelexcel-formulaopenoffice-calc
CodZilla
CodZilla

Reputation: 151

How to get a range of values from an openoffice calc formula?

Am encountered an issue on openOffice Calc.
I need to get a range of values from a formula, that can be used in a dropdownlist/validity.
I have a sheet with following data.

      A    B    C
  +---------------
1 |  10    x  
2 |  20    x  
3 |  30    y  
4 |  40    z  
5 |  50    x  
6 |---------------

Here I need a list of values of 'A' where values 'B' equals 'x'.
I have checked with LOOKUP/INDEX functions, but it returns a single value(first occurrence) not a range.

Upvotes: 2

Views: 1327

Answers (1)

Jerry
Jerry

Reputation: 71548

Try this formula:

=IFERROR(INDEX($A$1:$A$5, SMALL(IF($B$1:$B$5="x",ROW($B$1:$B$5),9^99),ROW())),"")
               ^-------^           ^-------^  ^      ^-------^  ^--^
                   |                   |      |          |       +-> Random big number
                   |                   |      |          +-> Range to check
                   |                   |      +-> Value to check
                   |                   +-> Range to check
                   +-> Range to return

You'll need to use Ctrl+Shift+Enter to make it work, then drag the formula down.

If you start on row 2, you'll have to use ROW()-1 for it to work. It's generally ROW()-(k-1) where k is the row number you're using the formula first.

Upvotes: 3

Related Questions