VBA - vlookup returning a range instead of single value

[Marc88]

Hi there, I'm quite new to VBA and was trying to find a solution from previous threads to this question but I couldn't.

I would like to find a value on the left column and return a range of values on the same row: a sort of vlookup but returning a range instead of a single value.

In the simplified example below it would be: look for Animals in column B, copy Bird, Horse, Cat, Dog and paste them in another sheet. Then repeat for colours and paste Blue, Red, Yellow, Green in the second sheet below the previous.

Is it possible with a VBA vlookup or is necessary another function?

thanks!
Marc

 

[DanteAmor]

Hi and welcome to MrExcel!

Where are you going to specify that you want to copy Animals or colors?
Is it always looking in column B?
Is it always copying columns E to H?
Where to paste?
What are the sheets called: origin and destination?
It would be nice if you put another image with the result of the first example.
_____________________________________________________________________________________

You can also use Vlookup, as follows, put the formula in B2 and copy to the right

Dante Amor
	A	B	C	D	E
1
2	Animals	Bird	Horse	Cat	Dog
Sheet2

Cell Formulas
Range	Formula
B2:E2	B2	=VLOOKUP($A2,Sheet1!$B$3:$H$10,COLUMNS($B$1:B1)+3,0)

 

[Marc88]

Thanks DanteAmor, here's the full situation:
there are 3 files in a folder (in reality I have >50 files but here I simplify)

These are the contents of the 3 files:

And this is what I would like to get, in another file, let's call it "Results": copy and pasting the content of the 3 files following the same group order

Here i did it manually cause there were only 3 files but i wonder if it's possible to do it via VBA when the files are many more...

Thanks
Marc