Extract row data from a criteria in one column

%3CLINGO-SUB%20id%3D%22lingo-sub-1475108%22%20slang%3D%22en-US%22%3EExtract%20row%20data%20from%20a%20criteria%20in%20one%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1475108%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20take%20the%20data%20from%20an%20entire%20row%20in%20the%20table%20(all%20columns)%20based%20on%20the%20criteria%20of%20one%20Column%20in%20that%20row.%26nbsp%3B%20Also%20needs%20to%20pull%20data%20from%20multiple%20rows%20based%20on%20that%20same%20criteria%20from%20that%20one%20column.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1475108%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1475191%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20row%20data%20from%20a%20criteria%20in%20one%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1475191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703903%22%20target%3D%22_blank%22%3E%40james0114%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20FILTER()%20could%20work.%20From%20your%20sample%20it's%20not%20clear%20which%20criteria%20to%20use.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1475453%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20row%20data%20from%20a%20criteria%20in%20one%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1475453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20I%20am%20trying%20to%20filter%20by%20Payment%20Type%20in%20column%205.%20But%20I%20need%20to%20take%20each%20type%20of%20payment%20to%20a%20different%20table.%20Payment%20types%20are%20Cash%2C%20Debit%2FCredit%2C%20Check%2C%20Bill%20To.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1476573%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20row%20data%20from%20a%20criteria%20in%20one%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1476573%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703903%22%20target%3D%22_blank%22%3E%40james0114%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20FILTER()%20is%20available%20for%20your%20version%20of%20Excel%2C%20you%20may%20use%20it%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(%24B%243%3A%24G%2412%2C%24F%243%3A%24F%2412%3D%24A%2415)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20not%2C%20for%20the%20range%20as%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20559px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199661iFF6B124A29C7902D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20apply%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(INDEX(F%243%3AF%2412%2CAGGREGATE(15%2C6%2C1%2F(%24F%243%3A%24F%2412%3D%24A%2415)*(ROW(%24F%243%3A%24F%2412)-ROW(%24F%242))%2CROW()-ROW(%24F%2416)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Edrag%20it%20down%20till%20end%20of%20the%20range%2C%20after%20that%20entire%20column%20to%20the%20left%20and%20to%20the%20right.%3C%2FP%3E%0A%3CP%3EBoth%20variants%20are%20in%20attached%20file.%20Power%20Query%20is%20also%20the%20variant%2C%20but%20from%20my%20point%20of%20view%20unnecessary%20for%20such%20case.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%2C%20I%20don't%20recommend%20to%20us%20Merge%20%26amp%3B%20Center%2C%20that%20could%20be%20a%20lot%20of%20side%20effects%20with%20merging.%20Alternatively%2C%20select%20all%20cells%2C%20Ctrl%2B1%20and%20apply%20Center%20across%20selection.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20554px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199662i3035181DF5FC9A1C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EResult%20will%20be%20the%20same%20as%20with%20merging%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20573px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199663i9A819AC3E431CF75%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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. 

3 Replies

@james0114 

In general FILTER() could work. From your sample it's not clear which criteria to use.

@Sergei Baklan  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. 

@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

image.png

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.

image.png

Result will be the same as with merging

image.png