Forum Discussion
How to copy row data matching specific column criteria
I'm a beginning Excel user. Still learning terms. I want to copy row data that matches specific column data. The column data I want to match on is composed of Sales Contact names. The Sales contact...
Since you have not shares any sample data as well as expected output, therefore I've assumed the data set along with the criteria, and would like to show few method, that how could you extract record/records.
How it works:
- Cell G10, G18 and G24 has criteria to match with related records.
Method 1:
- Extract multiple rows match the criteria in one column
- Enter this array (CSE) formula in cell F12, finish with Ctrl+Shift+Enter, and fill across.
{=IFERROR(INDEX($A$12:$D$21,SMALL(IF(COUNTIF($G$10,$C$12:$C$21),ROW($A$12:$D$21)-MIN(ROW($A$12:$D$21))+1),ROW(A1)),COLUMN(A1)),"")}
Method 2:
- Get one row match with the criteria.
- Formula in cell F21, finish with Enter, and fill Right.
=VLOOKUP($G$18,$A$12:$D$21,COLUMN(A1),FALSE)
Method 3:
- Get one row match with the criteria.
- Formula in cell F26, finish with Enter, and fill Right.
- This formula in performing REVERSE LOOKUP, since the LOOKUP value (the criteria) is not in first column.
=VLOOKUP($G$24,CHOOSE({1,2},$C$12:$C$21,A12:A21),2,0)
N.B. Adjust cell references and criteria in formula as needed.
Hi I happened to see this excel forum and I am trying to auto update the main data column that has a date column for contract.
How auto update into a new worksheet if the contract has dates, I need the entire row of information to be duplicate same row of info into another sheet
I cannot understand the above.. - {=IFERROR(INDEX($A$12:$D$21,SMALL(IF(COUNTIF($G$10,$C$12:$C$21),ROW($A$12:$D$21)-MIN(ROW($A$12:$D$21))+1),ROW(A1)),COLUMN(A1)),"")}
what the small ???
Please kindly help. Is there any video to see..
Thank you
How auto update into a new worksheet if the contract has dates, I need the entire row of information to be duplicate same row of info into another sheet
I cannot understand the above.. - {=IFERROR(INDEX($A$12:$D$21,SMALL(IF(COUNTIF($G$10,$C$12:$C$21),ROW($A$12:$D$21)-MIN(ROW($A$12:$D$21))+1),ROW(A1)),COLUMN(A1)),"")}
what the small ???
Please kindly help. Is there any video to see..
Thank you
- The formula you copied is an array formula used in the older Excel version before Dynamic Arrays was introduced. If you have Excel 365 you should consider the newer formulas like the FILTER formula I used above. If you have an older Excel version than you may need this type of formula. To answer you specific question the SMALL will simply return the X smallest value from an array. In this equation it uses ROW(A1) as the X and since A1 will increment for each cell it fills down to that is a way for the 1st cell in the column to return the 1st smallest value then the 2nd row returns the 2nd smallest value, etc... The array it is picking from is IF it matches then return the relative ROW where that match is located. And then finally the INDEX around the outside of it will take that relative ROW location and use it to look up the actual desired value. Hope that helps.
