Forum Discussion

james0114's avatar
james0114
Copper Contributor
Jun 18, 2020

Extract row data from a criteria in one column

I need to take the data from an entire row in the table (all columns) based on the criteria of one Column in that row.  Also needs to pull data from multiple rows based on that same criteria from that one column. 

excel
Excel for web
Formulas and Functions
office 365

3 Replies

    • james0114's avatar
      james0114
      Copper Contributor
      Jun 18, 2020

      SergeiBaklan  I am trying to filter by Payment Type in column 5. But I need to take each type of payment to a different table. Payment types are Cash, Debit/Credit, Check, Bill To. 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 19, 2020

        james0114 

        If FILTER() is available for your version of Excel, you may use it as

        =FILTER($B$3:$G$12,$F$3:$F$12=$A$15)

        If not, for the range as here

        you may apply formula

        =IFERROR(INDEX(F$3:F$12,AGGREGATE(15,6,1/($F$3:$F$12=$A$15)*(ROW($F$3:$F$12)-ROW($F$2)),ROW()-ROW($F$16))),"")

        drag it down till end of the range, after that entire column to the left and to the right.

        Both variants are in attached file. Power Query is also the variant, but from my point of view unnecessary for such case.

         

        In addition, I don't recommend to us Merge & Center, that could be a lot of side effects with merging. Alternatively, select all cells, Ctrl+1 and apply Center across selection.

        Result will be the same as with merging

Resources