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

SleightOfHand

New Member
Joined
Jun 19, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
How about
+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
Admin
Cell Formulas
RangeFormula
A16:F20A16=FILTER(A1:F8,ISNUMBER(MATCH(A1:A8,A12:A14,0)))
Dynamic array formulas.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
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
Admin
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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,126,944
Messages
5,621,753
Members
415,854
Latest member
Tutu123

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top