# How to extract entire row from a matched value, but that matched value has duplicates in the INDEX?

#### SleightOfHand

##### New Member
I don't know if this is possible just with one formula; but I guess it's worth a shot.

As you can see below there is a table A1:F8. These are tables with transport Lines and their corresponding stations, and other values.

Book1
ABCDEFG
1LINECode 1Code 2Station (First)Station (next)Volume
2Line 154454334433423Station AStation B100
3Line 144334239484334Station BStation C100
4Line 288768688549594Station XStation Y500
5Line 285495944998989Station YStation AB500
6Line 495832239444954St. XYZSt. ABC900
7Line 512345677898989Station AStation B100
8Line 698793899584938Station NStation ONone
9
10
11
12Line 154454334433423Station AStation B100
13Line 2
14Line 5
15
16
Sheet1
Cell Formulas
RangeFormula
B12:F12B12=VLOOKUP(\$A12,\$A\$2:\$F\$8,COLUMN(B\$1),FALSE)

Based on separate formulas, I have extracted Lines that are relevant to me; the others I do not care about (A12:A14). As these are part of a larger table, the relevant lines need to be extracted out in all the rows.
It's fine to extract the entire row from the first bit of Line 1, but I don't know how to modify it for the second value or indeed the second line -- the issue is that there are multiple values in Column A that have duplicates, so it would need to not only match it first the first value, but also be able to 'reset' and collate the corresponding rows for the second.

If there was only a unique Line in the table itself, then it would be easy, but as there are many of the same (duplicates) I'm not sure how to allow that.

Many thanks

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Fluff

##### MrExcel MVP, Moderator
+Fluff New.xlsm
ABCDEF
1LINECode 1Code 2Station (First)Station (next)Volume
2Line 154454334433423Station AStation B100
3Line 144334239484334Station BStation C100
4Line 288768688549594Station XStation Y500
5Line 285495944998989Station YStation AB500
6Line 495832239444954St. XYZSt. ABC900
7Line 512345677898989Station AStation B100
8Line 698793899584938Station NStation ONone
9
10
11
12Line 1
13Line 2
14Line 5
15
16Line 154454334433423Station AStation B100
17Line 144334239484334Station BStation C100
18Line 288768688549594Station XStation Y500
19Line 285495944998989Station YStation AB500
20Line 512345677898989Station AStation B100
Cell Formulas
RangeFormula
A16:F20A16=FILTER(A1:F8,ISNUMBER(MATCH(A1:A8,A12:A14,0)))
Dynamic array formulas.

#### Fluff

##### MrExcel MVP, Moderator
If you don't yet have the Filter function.

+Fluff New.xlsm
ABCDEF
1LINECode 1Code 2Station (First)Station (next)Volume
2Line 154454334433423Station AStation B100
3Line 144334239484334Station BStation C100
4Line 288768688549594Station XStation Y500
5Line 285495944998989Station YStation AB500
6Line 495832239444954St. XYZSt. ABC900
7Line 512345677898989Station AStation B100
8Line 698793899584938Station NStation ONone
9
10
11
12Line 1
13Line 2
14Line 5
15
16Line 154454334433423Station AStation B100
17Line 144334239484334Station BStation C100
18Line 288768688549594Station XStation Y500
19Line 285495944998989Station YStation AB500
20Line 512345677898989Station AStation B100
21
22
23
24
Cell Formulas
RangeFormula
A16:F22A16=IFERROR(INDEX(A\$2:A\$8,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1)/(ISNUMBER(MATCH(\$A\$2:\$A\$8,\$A\$12:\$A\$14,0))),ROWS(A\$16:A16))),"")

Replies
2
Views
420
Replies
9
Views
136
Replies
0
Views
98
Replies
4
Views
179
Replies
5
Views
305

1,128,177
Messages
5,629,182
Members
416,370
Latest member
Lgathana

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back