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

 

[Aladin Akyurek]

Hi Aladin,

Pretty and pretty slick!!!
I took my lookup formula and at the end added &" "& and then your formula and now I get my lookup value AND the sheet name in the same cell.

b1591 Sheet8

What could be better???

Thanks a ton!

Regards,
Howard,[TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

You are welcome.

Very impressed, can't follow what's happening there but it does the job! My Google searches found this question asked many times on forums and nobody gave a direct answer.

Thankyou
Tania

You are welcome. Here how it works:

The COUNTIF bit returns an array of counts, each count corresponding to a sheet. Something like:

{0;1;0}

corresponding to say Sheet1, Sheet2, and Sheet3,

Dividing 1 by this array of values we get:

{#DIV/0!;1;#DIV/0!}

LOOKUP looking at this array picks out the last numeric value (here it's 1) and correlates that positionwise with the sheet set of {"Sheet1";"Sheet2";"Sheet3"} and returns Sheet2 as result.

 

[pink9211]

Howard,

Can you share th efull formula which returned the sheet name and the cell values?

Thanks

Hi Aladin,

Pretty and pretty slick!!!
I took my lookup formula and at the end added &" "& and then your formula and now I get my lookup value AND the sheet name in the same cell.

b1591 Sheet8

What could be better???

Thanks a ton!

Regards,
Howard,[TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Howard,

Can you share th efull formula which returned the sheet name and the cell values?

Thanks

Something like...

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A100"),$A4),SheetList)
   &" "&VLOOKUP($A2,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,
      COUNTIF(INDIRECT("'"&SheetList&"'!A2:A10"),A2)>0,0))&"'!A2:C10"),2,0)

 

[L. Howard]

Howard,

Can you share th efull formula which returned the sheet name and the cell values?

Thanks

Sure, here it is and its pretty long. Along with all the problems of entering really long formulas into the formula bar.

This formula return includes "The LookUp Value" followed by the verbage "On Sheet -" and then "The Sheet Name" ie.

1234 On Sheet - Sheet3

The VLOOKUP formula:
MySheets is a Named Range of a list of the sheets.
The LookUp value is in B1.
The Look_Up Array is A2:B11 on each sheet.
The Column Index is 2.
The Fourth Argument is 0, an Exact Match.

The sheet name formula:
The LookUp value is in B1.
The Look_Up Array is A2:A11 on each sheet.

Formula is an Array Formula… Ctrl + Shift + Enter instead of just Enter.

Howard

=VLOOKUP(B1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A11"),B1)>0),0))&"'!A2:B11"),2,0)& " On Sheet - "
&LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&MySheets&"'!A2:A11"),B1),MySheets)

Here are the two formulas separate with a "space" joiner with no verbage.

VLOOKUP(B1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A11"),B1)>0),0))&"'!A2:B11"),2,0)

&" "&

LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&MySheets&"'!A2:A11"),B1),MySheets)

 

[celias]

How should we change the formula below if the item that we are looking for can appear in more than one sheet and we just want to return the name of the first sheet on the sheetlist?

Thank you in advance.

You are welcome.



You are welcome. Here how it works:

The COUNTIF bit returns an array of counts, each count corresponding to a sheet. Something like:

{0;1;0}

corresponding to say Sheet1, Sheet2, and Sheet3,

Dividing 1 by this array of values we get:

{#DIV/0!;1;#DIV/0!}

LOOKUP looking at this array picks out the last numeric value (here it's 1) and correlates that positionwise with the sheet set of {"Sheet1";"Sheet2";"Sheet3"} and returns Sheet2 as result.

 

[Aladin Akyurek]

How should we change the formula below if the item that we are looking for can appear in more than one sheet and we just want to return the name of the first sheet on the sheetlist?

Thank you in advance.

Control+shift+enter, not just enter:

=INDEX(SheetList,MATCH(1,1/COUNTIFS(INDIRECT("'"&SheetList&"'!A2:A100"),$A4),0))

Note. Here is opted for COUNTIFS instead of COUNTIF (for there is no need for the latter on later systems).

 

[celias]

Thank you, Aladin! It worked and this was really helpful!
Thank you for your dedication and for sharing your knowledge.
Cheers!

 

[Aladin Akyurek]

Thank you, Aladin! It worked and this was really helpful!
Thank you for your dedication and for sharing your knowledge.
Cheers!

You are welcome.

 

[archdog13]

Hi Aladin! Thank you for sharing this it is an incredibly helpful formula!

Is there a way for the formula to work so that the value being looked up (in my case the name of companies) could return the names of multiple sheets that are within the range? (for those where it appears more than once)

Thank you!

 

[Aladin Akyurek]

Hi Aladin! Thank you for sharing this it is an incredibly helpful formula!

Is there a way for the formula to work so that the value being looked up (in my case the name of companies) could return the names of multiple sheets that are within the range? (for those where it appears more than once)

Thank you!

The array-processing formula you are referring to:

=INDEX(SheetList,MATCH(1,1/COUNTIFS(INDIRECT("'"&SheetList&"'!A2:A100"),$A4),0))

looks up a value that is in A4 and returns a single sheet name from SheetList where a match against A2:A100 is found.

Let A4 house a company name of interest...

In A6 control+shift+enter and copy down:

=IFERROR(INDEX(SheetList,SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&SheetList&"'!A2:A100"),$A$4)),
    ROW(INDIRECT("1:"&COUNTIFS(SheetList,"?*")))),ROWS($A$6:A6))),"")