Pulling a cell range from another worksheet

Worksheet 1 has values in cell range A3:B6
Worksheet 2 in A3:B5
Worksheet 3 in A3:B7.

Now, whenever in worksheet 4 for eg. worksheet 1 is present by vlookup, I want that data for cells A3:B6 are put into worksheet 4 into 2 separate columns.
I would be needing data from more than 1 worksheet, therefore the different cell ranges may also be an issue.

Administrative Note:

Welcome to the forum.

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Thanks.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

And "Dinner" - where does this appear? It seems overly complex and multiple worksheets should be avoided where possible !!!

It is generally easier to have a single input (data) sheet and, if required, extract data into separate sheets.

Of course, this in part depends how you are going to use your data but I could not see any obvious merit in having separate sheets.

You would have one normalised table on one worksheet only. Each item would be added as a record, so it would look like this:

AliGW on MS365 Insider (Windows) 64 bit

	B	C	D	E
3	Meal	Name	Ingredients	Quantity
4	Breakfast	Overnight oats	Oats	50
5	Breakfast	Overnight oats	Protein Power	1
6	Breakfast	Overnight oats	Carob Powder	1
7	Breakfast	Overnight oats	Oat milk	100
8	Breakfast	English Breakfast	Eggs	2
9	Breakfast	English Breakfast	Sausages	2
10	Breakfast	English Breakfast	Baked beans	1
11	Dinner	Poke Bowl	Rice	100g
12	Dinner	Poke Bowl	Ginger	1 tablespoon
13	Dinner	Poke Bowl	Seaweed	1 tablespoon
14	Dinner	Poke Bowl	Tofu	50g
15	Dinner	Poke Bowl	Pineapple	50g

Sheet: Sheet1

You could then filter the table, add summary values, etc. to your heart's content. It is rarely if EVER advisable to spread items across multiple worksheets.

Thank you

Original Post:

Thank you however what I need is that for example when in the results sheet, I get overnight oats as a result from a formula, through another formula (the one I need help with), I get the ingredients and quantity. Because if I do a vlookup using 'overnight oats', I will only get the first value

I think you are missing the point.

Your current way of working is totally inefficient and creates unnecessary problems: there should be no need to have any of these recipes on separare worksheets at all. Please explain WHY this is necessary, in your view.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.