Share via

How to find which worksheet a value is in.

Anonymous
2014-05-05T13:34:29+00:00

Is there a formula that I can use to just tell me what worksheet a value is in?  I have a value, but it can be in any of 3 worksheets. I don't need any values, just whether it is in this one or that one.  I would prefer it to return the name of the worksheet or even better the name in the formula format so that I can just reference that cell for for future formulas '????'! if possible.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2014-05-06T07:50:34+00:00

Assuming your value is in cell A1 of Sheet4 and you are looking to find out this value in Sheet1, Sheet2 and Sheet3.   Then give this formula in Sheet4.  I have restricted value to find out from a range of A1:Z100 of each sheet for efficent working of formula, you may change the range as per your need, smallest the range, the better one.

The formula will give you the exact cell address with sheet name.

=IFERROR(ADDRESS(SUMPRODUCT((Sheet1!$A$1:$Z$100=A1)*ROW(Sheet1!$A$1:$A$100)),SUMPRODUCT((Sheet1!$A$1:$Z$100=A1)*COLUMN(Sheet1!$A$1:$Z$1)),,,"Sheet1"),IFERROR(ADDRESS(SUMPRODUCT((Sheet2!$A$1:$Z$100=A1)*ROW(Sheet2!$A$1:$A$100)),SUMPRODUCT((Sheet2!$A$1:$Z$100=A1)*COLUMN(Sheet2!$A$1:$Z$1)),,,"Sheet2"),ADDRESS(SUMPRODUCT((Sheet3!$A$1:$Z$100=A1)*ROW(Sheet3!$A$1:$A$100)),SUMPRODUCT((Sheet3!$A$1:$Z$100=A1)*COLUMN(Sheet3!$A$1:$Z$1)),,,"Sheet3")))

Also, if you want a generalisable version of the above, which can be extended to any number of sheets, not just three, then, if you list all of your sheet names in a vertical range, G1:G3 say:

=IFERROR(ADDRESS(SUMPRODUCT((INDIRECT("'"&INDEX(G1:G3,MATCH(TRUE,INDEX(COUNTIF(INDIRECT("'"&G1:G3&"'!A1:Z100"),A1)>0,,),0))&"'!A1:Z100")=A1)*ROW($A$1:$Z$100)),SUMPRODUCT((INDIRECT("'"&INDEX(G1:G3,MATCH(TRUE,INDEX(COUNTIF(INDIRECT("'"&G1:G3&"'!A1:Z100"),A1)>0,,),0))&"'!A1:Z100")=A1)*COLUMN($A$1:$Z$100)),,,INDEX(G1:G3,MATCH(TRUE,INDEX(COUNTIF(INDIRECT("'"&G1:G3&"'!A1:Z100"),A1)>0,,),0))),"Not Found")

Regards

Was this answer helpful?

1 person found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-05-06T05:56:23+00:00

    Two users asked similar questions recently and I provided them each with a VBA macro solution.  In one case they wanted to know what sheet a particular value appeared in and how many times it appeared on that sheet.  In the second case they wanted to be able to enter several values (or just 1) and determine which sheets each value appeared in and how many times each appeared on any given sheet.  That discussion and the code for both solutions is here:

    http://answers.microsoft.com/en-us/office/forum/office_2007-excel/macro-help/6d37962a-6b03-427c-a606-07d48eb10a3e

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-05-06T03:35:49+00:00

    Assuming your value is in cell A1 of Sheet4 and you are looking to find out this value in Sheet1, Sheet2 and Sheet3.   Then give this formula in Sheet4.  I have restricted value to find out from a range of A1:Z100 of each sheet for efficent working of formula, you may change the range as per your need, smallest the range, the better one.

    The formula will give you the exact cell address with sheet name.

    =IFERROR(ADDRESS(SUMPRODUCT((Sheet1!$A$1:$Z$100=A1)*ROW(Sheet1!$A$1:$A$100)),SUMPRODUCT((Sheet1!$A$1:$Z$100=A1)*COLUMN(Sheet1!$A$1:$Z$1)),,,"Sheet1"),IFERROR(ADDRESS(SUMPRODUCT((Sheet2!$A$1:$Z$100=A1)*ROW(Sheet2!$A$1:$A$100)),SUMPRODUCT((Sheet2!$A$1:$Z$100=A1)*COLUMN(Sheet2!$A$1:$Z$1)),,,"Sheet2"),ADDRESS(SUMPRODUCT((Sheet3!$A$1:$Z$100=A1)*ROW(Sheet3!$A$1:$A$100)),SUMPRODUCT((Sheet3!$A$1:$Z$100=A1)*COLUMN(Sheet3!$A$1:$Z$1)),,,"Sheet3")))

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2014-05-06T02:04:13+00:00

    Hi,

    Do you atleast know the column in the three worksheets where the search string can be found?

    Was this answer helpful?

    0 comments