Formula to lookup ID in multiple worksheets then return a value

[tml2424]

I'm looking for a ofrmula to look up a cell value and find that value across multiple worksheets then return another field. I tried using a Filter countif indirect using a 3D name manger that should look up for multple tabs but I can't get it to work. Any other ideas would be appreciated.

Here's the formula that I think is close but not quite working.
=TEXTJOIN("; ",,FILTER(Sheetlist,COUNTIF(INDIRECT("'"&Sheetlist&"'!f1:f5000"),C4)))

[AliGW]

I don't understand the construct.

SheetList is this: ='Tab 1:Tab 3'!$F$4:$F$10

So what are you trying to do with this?

INDIRECT("'"&Sheetlist&"'!f1:f5000")

Obviously that would resolve to:

='Tab 1:Tab 3'!$F$4:$F$10'!f1:f5000

which will, of course, throw an error.

Explain in WORDS what you want the formula to do (I mean function, not the result of it, which I can see in your workbook) - what in your mind should this be doing mechanically?

[AliGW]

I've created two named ranges:

SheetListE: ='Tab 1:Tab 3'!$E$4:$E$10
SheetListF: ='Tab 1:Tab 3'!$F$4:$F$10

Then this:

=LET(E,VSTACK(SheetListE),F,VSTACK(SheetListF),TEXTJOIN(";",,FILTER(F,E=C4)))

[AliGW]

For my own amusement, and in case it's the next logical step for you, I refined the workbook a little. See what you think.

[windknife]

Another option, clean all exptected result in column D.

Try this in D4.

Please Login or Register  to view this content.

[AliGW]

Glad to have helped.

If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) 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.