Lookup multiple sheets and produce unique results

Sthomson1984 welcome to the forum.

I haven't looked at your upload yet, but the description suggests there may be ways to do this using functions and features not available in all versions of Excel. This is why we ask for a detailed profile.

Please tell us to what versions (years) this solution will apply and update your profile to reflect this.

Edit Also please tell us the maximum number of rows and columns in the largest sheet of source data you anticipate.

Thank you Sthomson1984.

What about the columns and rows ... oh and number of sheets? I presume it's more than just the two?

It would be easier if all the input data was in one sheet by adding column with Project ID.

It is much easier to extract from one sheet than to consolidate from many e.g report by project [which could be created dynamically].

t

Hi Sthompson1984- Please take a look at this. It gives you the desired output, but it's not as flexible as I'd like. Hope it helps. - Lee
Two formulas:
in B2
=IF(COUNTA('Project A'!B:B)<3,"",TEXT(OFFSET('Project A'!$A$1,MATCH(Summary!$A2,'Project A'!$A:$A,0)-1,COLUMN()-1),"0%")&" - Project A")

in B3
=IF(COUNTA('Project A'!B:B)<3,"",TEXT(OFFSET('Project B'!$A$1,MATCH(Summary!$A2,'Project B'!$A:$A,0)-1,COLUMN()-1),"0%")&" - Project B")

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Sthomson1984 you might want to have a look at the topic of "redimensioning" here:

https://excelxor.com/category/redimensioning/

and here:
https://excelxor.com/2016/04/08/adva...le-worksheets/

Redimensioning is just the first step. More challenges come with knowing what to do with the resulting array.

While it's doable IMPO it reinforces what JohnTopley states above.

Hi,

You're making matters much more complex than they need be by setting up your Summary sheet in such a fashion. In particular, not repeating the names in column A for each row pertaining to that name means that the required construction becomes quite resource-heavy.

I would suggest first doing precisely that, i.e. repeating the names in column A such that there are no blanks, after which, in B2, array formula**:

=VLOOKUP(A2,INDIRECT("'"&INDEX(SheetList,SMALL(IF(COUNTIF(INDIRECT("'"&SheetList&"'!A2:A100"),A2)>0,TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(SheetList))))),COUNTIF($A$2:$A2,$A2)))&"'!A2:XFD100"),COLUMNS($A:B),0)

Copy across and down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Edit:

I should've mentioned that I assumed that SheetList is a horizontal vector, either within an actual worksheet somewhere, e.g. Y1:Z1, or, if stored within Name Manager, then (for English-language versions of Excel) comma-separated, e.g.:

={"Project A","Project B"}

Adjustments will need to be made if you have in fact defined SheetList as a vertical vector, e.g. Y1:Y2, or:

={"Project A";"Project B"}

Regards

Not for me.

I used the workbook you provided, placed "John Smith" in A3 and "Paul Citizen" in A5, put the formula I provided in B2 (committed, as described, as an array formula) and copied down to B5, giving (after formatting the cells as %): 50%, 50%, 25%, 75%, as required.

Regards