Lookup to return multiple values horizontally

Hi,

First time on the site today and found some very useful info.

However, I'm unable to find a way to return multiple values horizontally from different sheets on the same workbook.
I've seen various examples of this where the all the values are on the same worksheet, but have been unable to modify to fit my requirements.
I'm basically trying to return multiple values (Locations) on a per value (Circuit) basis:

I've attached a workbook (Book 1) which has the following:

Sheet 1
Shows Circuits (in some cases multiple instances) and their respective Locations

Sheet 2
Shows unique Circuit Reference values.

I would like to return the associated Location Reference horizontally against each Circuit Reference on Sheet 2 (have shown the required end result on the workbook).

Would appreciate any assistance here.

Thanks in advance

Rob

=VLOOKUP(A2,Sheet2!$A$2:$F$11,2+COUNTIF(Sheet1!$A$1:A1,A2),FALSE)

In B2 and copy down

Hi yudlugar,

Thanks for the quick reply.
Unfortunately, that seems to return a circular reference warning and then a value of '0' when applied.
Are you able to update my spreadsheet with the formula you've suggested?

Rob

Afraid attachments aren't working for me (sometimes blocked on this connection) I just double checked it on the example you uploaded and it works fine though. Just to double check you are putting it in cell B2 or sheet1?

I'm putting the formula on Sheet 2 Cell B2
So effectively overwriting the previous value that I had manually put there to show what results I was after.

ah ok sorry - I thought you wanted to create the list in sheet1 from the values in sheet2, to go the other way:
=IF(SMALL(IF(Sheet1!$A$2:$A$21=Sheet2!$A2,ROW(A$1:A$20),10000),COLUMN(A1))=10000,"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet1!$A$2:$A$21=Sheet2!$A2,ROW(A$1:A$20),10000),COLUMN(A1))))
as an array formula (confirm with ctrl+shift+enter) in Sheet2!B2 and copy across/down.

That works, thanks yudlugar.
Thanks also to AlKey for uploading the formula to the example sheet.
One quick check whilst you're both around! the sheet I uploaded was test data cut down for the purpose of obtaining the correct formula.
However, I've now transposed the correct data on there (much the same in terms of headers/values) but a lot more data, when I drag the formula down the page, it's only working up to Row 15, anything after that and it's not returning anything.
Is there anything in the formula to limit this to just the data rows on the original Book 1 sheet or should I be able to drag the formula?
Thanks again so far!

Attaching Book 2 sheet