VBA - vlookup returning a range instead of single value

Marc88

New Member
Joined
May 15, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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.

1589579324965.png


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

thanks!
Marc
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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
ABCDE
1
2AnimalsBirdHorseCatDog
Sheet2
Cell Formulas
RangeFormula
B2:E2B2=VLOOKUP($A2,Sheet1!$B$3:$H$10,COLUMNS($B$1:B1)+3,0)
 

Marc88

New Member
Joined
May 15, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Thanks DanteAmor, here's the full situation:
there are 3 files in a folder (in reality I have >50 files but here I simplify)
Capture1.PNG



These are the contents of the 3 files:

Capture2.PNG


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

Capture3.PNG

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
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    15.3 KB · Views: 1

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

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