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

 

[archdog13]

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))),"")

Hi Aladin,

Thank you so much for this. I've tried to employ your example, on an excel file which i've created as a simpler example of what i'm trying to do.

For the benefit of other users (i'm trying to avoid uploading a file) and to explain the photos below the following is in the sheets

• A list to look up consisting of animals that can appear in any of the subsequent sheets (potentially in all three): Dog, Cat, Dolphin, Iguana, Elephant, Tiger, Lion, Fish and Penguin. I want to return the name of the sheets these animals are found on
• Sheet names: James Peter and Simon I want to return data from which have been named as "SheetList"
• Potential data in cells: A1 -> A999 in those three sheets
  • James sheet: Dog, Cat, Tiger, Fish and Penguin
  • Peter: Lion, Fish, Penguin and Tiger
  • Simon: Tiger, Fish and Penguin

I don't seem to be able to get it to return all three of the names on my SheetList. "Peter" does not show at all up which is the middle of the named list

However, what i'm ideally trying to create is shown on the image below so that the sheet names would return across a series of columns. Would this be possible?

Thanks so much for your help - i'm relatively new to Excel so apologies for any stupid questions (wanted to make this as helpful as possible for other people in the future)

Arch

 

[Aladin Akyurek]

Hi Aladin,

Thank you so much for this. I've tried to employ your example, on an excel file which i've created as a simpler example of what i'm trying to do.

For the benefit of other users (i'm trying to avoid uploading a file) and to explain the photos below the following is in the sheets

• A list to look up consisting of animals that can appear in any of the subsequent sheets (potentially in all three): Dog, Cat, Dolphin, Iguana, Elephant, Tiger, Lion, Fish and Penguin. I want to return the name of the sheets these animals are found on
• Sheet names: James Peter and Simon I want to return data from which have been named as "SheetList"
• Potential data in cells: A1 -> A999 in those three sheets
  • James sheet: Dog, Cat, Tiger, Fish and Penguin
  • Peter: Lion, Fish, Penguin and Tiger
  • Simon: Tiger, Fish and Penguin

I don't seem to be able to get it to return all three of the names on my SheetList. "Peter" does not show at all up which is the middle of the named list

[...]

However, what i'm ideally trying to create is shown on the image below so that the sheet names would return across a series of columns. Would this be possible?

[...]

Thanks so much for your help - i'm relatively new to Excel so apologies for any stupid questions (wanted to make this as helpful as possible for other people in the future)

Arch

I'm not sure the task you describe above is the same as the one the formula under discussion tackles with. Moreover, the data on the 'photos' require retyping in order to be of effective use.

Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl
is also a method for posting an exhibit.<strike></strike>

 

[archdog13]

I'm not sure the task you describe above is the same as the one the formula under discussion tackles with. Moreover, the data on the 'photos' require retyping in order to be of effective use.

Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl
is also a method for posting an exhibit.<strike></strike>

Hi Aladin,

Sorry if this issue diverts from the original question I had thought it was along a similar line.

I have included below a link to the example spreadsheet I have constructed.

https://db.tt/vOaUDo4w

Thank you again for your help

Arch

 

[Aladin Akyurek]

Hi Aladin,

Sorry if this issue diverts from the original question I had thought it was along a similar line.

I have included below a link to the example spreadsheet I have constructed.

https://db.tt/vOaUDo4w

Thank you again for your help

Arch

Would you try to post the data and the results which must obtain, instead of "posting" formula results?

 

[archdog13]

Would you try to post the data and the results which must obtain, instead of "posting" formula results?

Hi Aladin,

Sorry i meant to attach the version now included on the link. I've made a table of what should show up as a hardcoded version to the right.

It would be a formula that would show the sheet name where the name of the type of animal appears, to show who owns what in this case. I hope i am making sense I just can't find a solution to this problem anywhere.

https://db.tt/zf32HtgY

Thank you,

Ed

 

[Aladin Akyurek]

Hi Aladin,

Sorry i meant to attach the version now included on the link. I've made a table of what should show up as a hardcoded version to the right.

It would be a formula that would show the sheet name where the name of the type of animal appears, to show who owns what in this case. I hope i am making sense I just can't find a solution to this problem anywhere.

https://db.tt/zf32HtgY

Thank you,

Ed

Ed,

You have a trailing space after Peter in the range of the sheet names in Archdog13 that you need to remove (here done so)...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

L​

[/td][/tr]
[tr][td]

4​

[/td][td]SheetList[/td][/tr]


[tr][td]

5​

[/td][td]James[/td][/tr]


[tr][td]

6​

[/td][td]Peter[/td][/tr]


[tr][td]

7​

[/td][td]Simon[/td][/tr]
[/table]

James

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][/tr]
[tr][td]

1​

[/td][td]Animals owned[/td][/tr]


[tr][td]

2​

[/td][td]Dog[/td][/tr]


[tr][td]

3​

[/td][td]Cat[/td][/tr]


[tr][td]

4​

[/td][td]Tiger[/td][/tr]


[tr][td]

5​

[/td][td]Fish[/td][/tr]


[tr][td]

6​

[/td][td]Penguin[/td][/tr]
[/table]
<strike></strike>
Peter

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][/tr]
[tr][td]

1​

[/td][td]Animals owned[/td][/tr]


[tr][td]

2​

[/td][td]Lion[/td][/tr]


[tr][td]

3​

[/td][td]Fish [/td][/tr]


[tr][td]

4​

[/td][td]Penguin[/td][/tr]


[tr][td]

5​

[/td][td]Tiger[/td][/tr]
[/table]
<strike></strike>

Simon

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][/tr]
[tr][td]

1​

[/td][td]Animals Owned[/td][/tr]


[tr][td]

2​

[/td][td]Tiger[/td][/tr]


[tr][td]

3​

[/td][td]Fish[/td][/tr]


[tr][td]

4​

[/td][td]Penguin[/td][/tr]


[tr][td]

5​

[/td][td]Iguana[/td][/tr]
[/table]

Archdog13

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

2​

[/td][td]Who owns what animal?[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td][/td][td][/td][td]

Formula​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

5​

[/td][td][/td][td]Animals[/td][td]

Name1​

[/td][td]

Name2​

[/td][td]

Name3​

[/td][/tr]


[tr][td]

6​

[/td][td][/td][td]Dog[/td][td]

James​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

7​

[/td][td][/td][td]Cat[/td][td]

James​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

8​

[/td][td][/td][td]Dolphin[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

9​

[/td][td][/td][td]Iguana[/td][td]

Simon​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

10​

[/td][td][/td][td]Elephant[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

11​

[/td][td][/td][td]Tiger[/td][td]

James​

[/td][td]

Peter​

[/td][td]

Simon​

[/td][/tr]


[tr][td]

12​

[/td][td][/td][td]Lion[/td][td]

Peter​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

13​

[/td][td][/td][td]Fish[/td][td]

James​

[/td][td]

Simon​

[/td][td]

​

[/td][/tr]


[tr][td]

14​

[/td][td][/td][td]Penguin[/td][td]

James​

[/td][td]

Peter​

[/td][td]

Simon​

[/td][/tr]
[/table]

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

=IFERROR(INDEX(SheetList,1/(1/SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&SheetList&"'!A2:A999"),$B6)),
    ROW(INDIRECT("1:"&COUNTIFS(SheetList,"?*")))),COLUMNS($C6:C6)))),"")<strike></strike>

Note. Compared with the original: ROWS >> COLUMNS and 1/(1/...) added for error-driven output control.

 

[Milenche]

Hi Aladin,

I would like to go one step further and add an IF function related to the date.

I am using this to look up the initials of resources in project tabs to bring back the project numbers (sheet name) that each resource is working on. However, if the project is closed I don't want the sheet name to be listed. There is a project end date cell in each sheet. How can I add the test TODAY()>Project End Date for each sheet to determine if the sheet name will be returned.

Thank you in advance for your help!


Milenche

 

[Aladin Akyurek]

Hi Aladin,

I would like to go one step further and add an IF function related to the date.

I am using this to look up the initials of resources in project tabs to bring back the project numbers (sheet name) that each resource is working on. However, if the project is closed I don't want the sheet name to be listed. There is a project end date cell in each sheet. How can I add the test TODAY()>Project End Date for each sheet to determine if the sheet name will be returned.

Thank you in advance for your help!


Milenche

That would require expanding the COUNTIFS bit of the formula with a date test applied to the range 'Project End Date'.

 

[Foz1980]

Create a range housing the names of the relevant sheets. Select this range and name it SheetList.

Assuming that the look up value in $A4 appears say in A2:A100 on some relevant sheet, invoke:

=LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A100"),$A4),SheetList)

Hi,

Sorry for re-opening an old thread but I'm trying to find a solution to this issue.
Can you tell me how to create a range housing the names of the relevant sheets please?

Cheers

Chris

 

[Aladin Akyurek]

Hi,

Sorry for re-opening an old thread but I'm trying to find a solution to this issue.
Can you tell me how to create a range housing the names of the relevant sheets please?

Cheers

Chris

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.