Lookup value across multiple sheets and return sheet name

[Tanianiania]

Hello

I'm struggling with this one..

I need a formula that reference a value starting in $A4 and looks for it across multiple worksheets and returns the worksheet name

There are 8 worksheets in total, the value will only appear once and won't be duplicated across sheets.

Thankyou

 

[Foz1980]

Let's say that the relevant sheet are: Sheet1, Sheet2, and Sheet3.

Insert a new worksheet and rename this new sheet Admin.

Go the Admin.

In A2 enter: Sheet1
In A3 enter: Sheet2
In A4 enter: Sheet3

Select A2:A4, type SheetList in the Name Box on the left side of the Formula Bar.

You can now use SheetList anywhere in your workbook where you need it.

That's awesome! Thanks so much

 

[Aladin Akyurek]

That's awesome! Thanks so much

You are welcome.

 

[forginganewone]

Google lead me to this, I was looking for the same solution. ALADIN MY MAN ! saved me atleast 3 hours of work. I wish you well.

 

[Aladin Akyurek]

Google lead me to this, I was looking for the same solution. ALADIN MY MAN ! saved me atleast 3 hours of work. I wish you well.

Just great. Thank you for noting that.

 

[forginganewone]

Just great. Thank you for noting that.

I just have 1 little question, i am using this formula :
=IFERROR(INDEX(Sheetlist,1/(1/SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&Sheetlist&"'!D:D"),$B7)),
ROW(INDIRECT("1:"&COUNTIFS(Sheetlist,"?*")))),COLUMNS($K6:K6)))),"")

What if the sheets are on a different workbook ? how do i link them ?
Lets say the path is C:\Desktop\workbook1.xls
Sheet names in that workbook are 1 , 2 , 3 , 4

I have to do this because putting the sheets in the same workbook and making it very heavy.

 

[Aladin Akyurek]

I just have 1 little question, i am using this formula :
=IFERROR(INDEX(Sheetlist,1/(1/SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&Sheetlist&"'!D:D"),$B7)),
ROW(INDIRECT("1:"&COUNTIFS(Sheetlist,"?*")))),COLUMNS($K6:K6)))),"")

What if the sheets are on a different workbook ? how do i link them ?
Lets say the path is C:\Desktop\workbook1.xls
Sheet names in that workbook are 1 , 2 , 3 , 4

I have to do this because putting the sheets in the same workbook and making it very heavy.

Neither COUNTIFS nor INDIRECT work with closed workbooks, alas.

 

[MennoVK]

Hi I used this formula from this post;
=LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&Sheetlist&"'!B8:Q8"),A8),Sheetlist)
I also need to lookup a value across multiple sheets an return the sheet name.
But when the value is in multiple sheets I all the sheet names in which the value is found.
Is there a way of doing this ?

 

[Aladin Akyurek]

Hi I used this formula from this post;
=LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&Sheetlist&"'!B8:Q8"),A8),Sheetlist)
I also need to lookup a value across multiple sheets an return the sheet name.
But when the value is in multiple sheets I all the sheet names in which the value is found.
Is there a way of doing this ?

Assuming that SheetList is defined properly and we want the sheet names listed in B8 and to the right...

In B8 control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX(SheetList,SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&SheetList&"'!B8:Q8"),$A8)),
    ROW(INDIRECT("1:"&COUNTIFS(SheetList,"?*")))),COLUMNS($B$8:C8))),"")

 

[ariverr]

Hi All,
Does anybody know how can I adapt Aladins' formula for use in Google Sheets?
I tried to implement it "as is" (with SheetList range and relevant cell ranges to my workbook) but it returns the following error:

Error
Did not find value '1E+308' in LOOKUP evaluation.

 

[Feredzhanov]

You can create a name range: Formula -> Name Manager -> New, you can name is SheetList (as used in previous examples), Scope: Workbook, Refer to:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

That way you don't need to create a list in a column for your range, it works automatically for all sheets in the workbook w/o manually updating the list. The above examples work with that method out of the box.