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.