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

 

[Tanianiania]

Also, if it makes any difference the value to lookup will be in Column D on the other sheets. Thankyou

 

[L. Howard]

Hi Tanianiania,

Can you offer up what the data on the eight lookup sheets looks like?
Is it just a fews rows in column D of each sheet that will hold the value?
Are all the column D's in each sheet the same number of rows?
Is there data in colums C and E of the sheets?

I have this vlookup by Peo S. that looks across all the worksheets named in the named range MySheets (five in my example) in the lookup_array D2:E200 and returns the value in column E.

The second formula returns the sheet name of the sheet it is entered in.(Woorkbook has to be saved before it will function, if not saved then #VALUE error) It may or may not be of help. I have tried to incorporate both formulas to return a value from one of five sheets AND sheet name. No luck! I have a truly clunky way of doing that but....I am reluctant to offer it up at this time.

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!D2:D200"),A1)>0),0))&"'!D2:E200"),2,0)
By: Peo Sjoblom

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

You could possibly put the respective sheet names in column E and copy down to cover all the possible lookup values you might look up. If E column is out of the question then you could put the sheet names 3, 4 or say 5 columns to the right and adjust the formula to return from one of those columns.

And with all this I assume you only want the sheet name returned.

Pretty long-winded MAYBE, eh?

Regards,
Howard

 

[Tanianiania]

Thanks, I see what you are trying to achieve here. The first part works as a V-lookup across multiple sheets, I got that bit working. But the second part just returns the name of the sheet I am currently on and not the sheet it found the value? Thanks, Tania

Hi Tanianiania,

Can you offer up what the data on the eight lookup sheets looks like?
Is it just a fews rows in column D of each sheet that will hold the value?
Are all the column D's in each sheet the same number of rows?
Is there data in colums C and E of the sheets?

I have this vlookup by Peo S. that looks across all the worksheets named in the named range MySheets (five in my example) in the lookup_array D2:E200 and returns the value in column E.

The second formula returns the sheet name of the sheet it is entered in.(Woorkbook has to be saved before it will function, if not saved then #VALUE error) It may or may not be of help. I have tried to incorporate both formulas to return a value from one of five sheets AND sheet name. No luck! I have a truly clunky way of doing that but....I am reluctant to offer it up at this time.

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!D2:D200"),A1)>0),0))&"'!D2:E200"),2,0)
By: Peo Sjoblom

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

You could possibly put the respective sheet names in column E and copy down to cover all the possible lookup values you might look up. If E column is out of the question then you could put the sheet names 3, 4 or say 5 columns to the right and adjust the formula to return from one of those columns.

And with all this I assume you only want the sheet name returned.

Pretty long-winded MAYBE, eh?

Regards,
Howard

 

[L. Howard]

You are correct and that is what I said in my post. I also said I could not get the two to work together, try as I may. (I posted to a forum like this and none of the Super Star MVP's offered a solution.)

So you are returning a value from one of the eight sheets, correct?
And you want to know what the sheet name that value came from, correct?

If so, perhaps try this:

Below the top formula returns the value from one of the sheets, D2:E200 (in the formula here) but adjusted to your workbook.
You said you had that working, apparently you knew to array enter the formula.

Now IF, repeat IF possible on your eight sheets in column F, enter that sheets name and pull down to cover the full column of the first formuls lookup_array.
Notice the second formula is a three colums lookup_array and the 3 instead of 2 for the column index.

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!D2:D200"),A1)>0),0))&"'!D2:E200"),2,0)
=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!D2:D200"),A1)>0),0))&"'!D2:F200"),3,0)

So, the first formula returns the value and the second goes one more column past that value and returns the sheet name.

I guess you could go 50 columns to the right to put a column full of sheet names if necessary. D2:AX200 and the column index would be 46.

Hope this can work for you. Let me know please.

Regards,
Howard

 

[Tanianiania]

Thanks Howard, I actually scoured a number of forum posts asking the same thing and not one of them answered the question.

In the end I did it the way you described, entering the sheet name in a column. Would have preferred not to because I'm analysing a number of old reports that were not built correctly by someone else and can't be re-run.

It's a bit tedious doing it for so many but looks like that's my option! And then re-build the report correctly.

Also, I wasn't actually trying to achieve a 'Vlookup' scenario.

I was just trying to locate which sheet an Equipment ID was on, I wasn't trying to return a vlookup value, just locate the IP and tell me which sheet it was on (like CTRL+F, but there were thousands)



Thankyou

You are correct and that is what I said in my post. I also said I could not get the two to work together, try as I may. (I posted to a forum like this and none of the Super Star MVP's offered a solution.)

So you are returning a value from one of the eight sheets, correct?
And you want to know what the sheet name that value came from, correct?

If so, perhaps try this:

Below the top formula returns the value from one of the sheets, D2:E200 (in the formula here) but adjusted to your workbook.
You said you had that working, apparently you knew to array enter the formula.

Now IF, repeat IF possible on your eight sheets in column F, enter that sheets name and pull down to cover the full column of the first formuls lookup_array.
Notice the second formula is a three colums lookup_array and the 3 instead of 2 for the column index.

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!D2:D200"),A1)>0),0))&"'!D2:E200"),2,0)
=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!D2:D200"),A1)>0),0))&"'!D2:F200"),3,0)

So, the first formula returns the value and the second goes one more column past that value and returns the sheet name.

I guess you could go 50 columns to the right to put a column full of sheet names if necessary. D2:AX200 and the column index would be 46.

Hope this can work for you. Let me know please.

Regards,
Howard

 

[Aladin Akyurek]

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

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)

 

[L. Howard]

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,

<colgroup><col style="width: 48pt;" width="64">
<tbody>

</tbody>

 

[L. Howard]

Tania,

Aladin's formula hits the nail smack on the head. That should easily solve your problem.

Regards,
Howard

 

[Tanianiania]

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)

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