• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return multiple values across multiple Sheets

T

tirmizi

New Member
Hi,

I am struggling with excel vlookup function, not even sure if it can be used in this case since I want to return multiple values. So I have a workbook with multiple worksheets as in the uploaded sample. The format of each sheet is exactly the same with column 3 showing 5 fruit names (names cant repeat within the column).

I want to add an extra column, in which each cell would have a function that searches for the corresponding fruit name (col 3) across all sheets, and returns the names of all sheets that have the same fruit anywhere in the 3rd column.

Is there any simple way of doing this or any hard way for that matter (Without VBA if possible)? I have searched the vlookup extensively, but cannot seem to make it work.

Any help would be highly appreciated!
 

Attachments

Since you have multiple results, presumably a matrix type output is acceptable. Will the attached suffice? You have to list out the sheet names before hand, but makes this task doable. Trying to extract sheet names would be much harder.
 

Attachments

Luke M said:
Since you have multiple results, presumably a matrix type output is acceptable. Will the attached suffice? You have to list out the sheet names before hand, but makes this task doable. Trying to extract sheet names would be much harder.
Click to expand...

Hi, thanks for your help, much appreciated!

Your way does get the job done, but unfortunately the report I am creating can't be formatted that way. Someone else suggested to use VBA and do a "for each worksheet in ActiveWorkbook" and then a "do until blank" loop within each sheet. Can you make sense of this, I have absolutely no knowledge of VBA but have some rudimentary programming knowledge so might try my hand at that.

Cheers!
 
Possibly...but it would be much slower than formulas. Before going full-on VB, take a look at this. Still uses formulas, but added a vb user defined function (UDF) to puts the sheet names together.

Since formatting is an issue, it would also be helpful if you told me what layout you are wanting.
 
Luke M said:
Possibly...but it would be much slower than formulas. Before going full-on VB, take a look at this. Still uses formulas, but added a vb user defined function (UDF) to puts the sheet names together.

Since formatting is an issue, it would also be helpful if you told me what layout you are wanting.
Click to expand...

I think you forgot to attach a file here.

I am looking for a format like the one attached (Sheet1). I know the one on left seems to be wishful thinking but the one on right is rather doable I hope?

Cheers!
 

Attachments

You must log in or register to reply here.
Back
Top