Help with multiple Index

[Hamilton013]

My Table

Ref# Price Trans Price Trans Type 2
1 476.4 9313 476.4 10915 0
2 413.8 1582 413.8 2183 0
3 542.8 7751 542.8 10045 0
4 612.3 1131 412.3 1822 1
5 374.08 10074 374.1 10074 0
6 196.9 771 196.9 1973 0
7 529.3 5969 466.3 8783 1
8 265.6 2384 255.4 2484 1
9 268.3 4937 287.3 4637 0
10 467.6 7571 452.6 8272 1


What I need is to lookup each instance that Type 2 is value 1, and return the Ref # in a concatenated cell.

I am using: =INDEX(A5:A404, MATCH(1, H5:H404, 0), 1)
but it will only change once it has passed the first instance of 1 in Type 2.

Example:

Ref# Price Trans Price Trans Type 2
1 476.4 9313 476.4 10915 0 4
2 413.8 1582 413.8 2183 0 4
3 542.8 7751 542.8 10045 0 4
4 612.3 1131 412.3 1822 1 4
5 374.08 10074 374.1 10074 0 7
6 196.9 771 196.9 1973 0 7
7 529.3 5969 466.3 8783 1 7
8 265.6 2384 255.4 2484 1 8
9 268.3 4937 287.3 4637 0 8
10 467.6 7571 452.6 8272 1 10

My sheet has approx 4K Ref # and I have several sheets.

[olasa]

To concatenate several values, the best thing is to install the Morefunc Add-in:
http://xcell05.free.fr/ click on English pages. Use the MCONCAT function.

Example:
=MCONCAT(IF(H5:H404=1,A5:A404&" ",""))

It's not easy to concatenate an Array with normal Excel functions or formulas.

Hope it helped
Ola Sandström

[Hamilton013]

Perfect! Thanks a bunch. That saves me hours.