Thank you!

A thank you would suffice

Will give it a whirl, if it works will you accept Czechs?

You can't concatenate an array without adding another function so one way or another you'll be using VB editor.

The easiest way to do this would be like so...

Hit Alt + F11 to open VB editor and paste the following into a module (Insert > Module) to make a new concatenate function

quote:

---

Function Combine(Rng As Range) As String
Dim C As Range
For Each C In Rng.Cells
Combine = Combine & C.Value & " "
Next C
End Function

---

quote:

---

=IF(COUNTBLANK(A1:B1)>0,"Range not specified",combine(INDIRECT("Sheet1!B"&A1&":B"&B1)))

---

Infact no, INDEX MATCH won't reference the cell's in-between your numbers.

Whats the end goal with having this data in the way you have described? There may be a better way to change this around

I'm sure you could manipulate a Index Match formula to do this but I think (without actually having a go) the data will need to be transposed with headers

[Edited on 22-06-2015 by Gaz]

I'd have thought that filtering would work better for what you are trying to describe and using names?

Need a formula that I'm not even sure exists, best way I can explain it is some sort of range lookup concatenate

I'll try and keep this as simple as possible

Sheet 1,

Consecutive reference numbers in column a
Names in column b e.g.

1 matthew
2 mark
3 luke
4 john
5 Steven
6 joe
7 lee
8 sam
9 michael
10 Peter

Sheet 2,

Column 1 has the first number in a range
Column 2 the last number in the range
Column 3 I need to bring back all the names for that specific range e.g.

1 4 matthew mark luke john
5 7 Steven joe lee
8 10 sam michael Peter

Not too fussed if these are spaced out or not as I can sort that later

Any help greatly appreciated