Lookup a value in multiple sheets and return name of sheet

[celias]

Hello!
I have the following problem:
I have one workbook with 36 sheets.
All the sheets are alike: same structure, same column names. The data varies.
On each sheet I would like to look for each one of the Column 1 values (Member_Name) on each one of the previous sheets. If the name is there (and it can be on multiple sheets), then I would like to know the name of the first sheet where that name exists.

I started trying with IFNA combined with VLOOKUP, but having to nest up to 36 formulas doesn't work...
Among other options, I also tried using
=LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A2900"),$A2),SheetList) after creating the sheets list, but this gives me the last sheet on which the member_name appears, and I wanted the first...

Any help?
Thank you in advance for your attention, your time and your help.

 

[FormR]

but this gives me the last sheet on which the member_name appears, and I wanted the first...

Hi, one option would be to simply sort your SheetList, another possible option is:

=INDEX(SheetList,MATCH(TRUE,INDEX(ISNUMBER(1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A2900"),$A2)),0),0))

 

[celias]

Hi, one option would be to simply sort your SheetList, another possible option is:

=INDEX(SheetList,MATCH(TRUE,INDEX(ISNUMBER(1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A2900"),$A2)),0),0))

Hi, @FormR! Thank you for answering.
The list is sorted - sheets are named M1 to M36. Is this what you mean?

The formula doesn't seem to work.
On Sheet M2, the members listed there on column A could either be on M1 as well, or just in M2.
So, the expected results on this sheet should be M1 or M2.
And I get all kind of list numbers up until M36 and even N/A. The value returned is neither the first sheet where the member_name is nor the last.

Am I entering something wrong?
Any other suggestion?

Thank you.

 

[FormR]

Hi, how about posting a few specific examples of where it doesn't work? Try to include the results you get and the results that you expected.

 

[celias]

Hi, how about posting a few specific examples of where it doesn't work? Try to include the results you get and the results that you expected.

OK.
Here is the case: 36 sheets. same first row and 4 columns in all of them. Columns titles are: Customer Name, Type, Amount, Refund.
Each sheet refers to one month. I am studying a period of 3 years. Sheets are named M1, M2, M3, ..., M36.
Each sheet has a variable number of rows that can go up to 2900.

On each month I have a list of customer names on column A. Some customers already existed on the sheets for the previous months, other customers are new that month. It may also happen that a customer name figures on, for example, M1, M2 and M3. Then it doesn't appear on M4, M5, M6, M7 and it comes again on M8. And so it goes up until M36. The name can be on a variable number of sheets.

On each sheet, for each name listed on Column A, I would like excel to look into all the sheets before that one and say what is the first month on which that name apears (when that customer first became a member).
So, if I am looking on sheet M6, the expected results for each one of the names on Column A of that sheet can be, M1, M2, M3, M4, M5 or M6.

With the formula that you suggested, I was getting results like "M10" and "N/A" on sheet M2.

I hope I was able to explain it well.

Multiple VLOOKUPS nested inside IFNA formulas would work if I didn't have so many sheets.
For example, I named column E (New on month) and I was using on Column E of sheets M1 to M4 the following:

M1 1 (meaning that on month 1, all new customers are new on that month)
M2 =IFNA(VLOOKUP(A2,'M01'!A:E,5,FALSE),2)
M3 =IFNA(VLOOKUP(A2,'M01'!A:E,5,FALSE),IFNA(VLOOKUP(A2,'M02'!A:E,5,FALSE),3))
M4 =IFNA(VLOOKUP(A11,'M01'!A:E,5,FALSE),IFNA(VLOOKUP(A11,'M02'!A:E,5,FALSE),IFNA(VLOOKUP(A11,'M03'!A:E,5,FALSE),4)))


What I am asking Excel is: Hey, look for this name on sheet M1. If it is there say "1" (meaning month#1), if not, go look on sheet number 2. If the name is there, say "2" (meaning month #2), if not, go look into sheet number 3. and so on...


At some point I get lost with the formula as it gets too big.

I have searched and found different approaches but haven't been able to adapt one successfully.

Than you very much for helping.

 

[FormR]

HI, I'm fairly sure that the formula supplied does what you are looking for: here is a small sample using 4 sheets - the same formula is used in column D of all sheets. If you are still having difficulties - maybe you could upload a sanitised cutdown version of your workbook to a file file sharing site (like dropbox) and share the link here.

SheetList:

Excel 2012
	A
1	M1
2	M2
3	M3
4	M4
Sheet1

M1

Excel 2012
	A	B	C	D	E
1	Name				Formula
2	A				M1
3	C				M1
M1

Cell Formulas
Range	Formula
E2	=INDEX(SheetList,MATCH(TRUE,INDEX(ISNUMBER(1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A2900"),$A2)),0),0))

Named Ranges
Name	Refers To	Cells
SheetList	=Sheet1!$A$1:$A$4

M2

Excel 2012
	A	B	C	D	E
1	Name				Formula
2	A				M1
3	C				M1
4	D				M2
M2

M3

Excel 2012
	A	B	C	D	E
1	Name				Formula
2	A				M1
3	B				M3
4	C				M1
5	D				M2
M3

M4

Excel 2012
	A	B	C	D	E
1	Name				Formula
2	A				M1
3	B				M3
4	F				M4
M4

 

[FormR]

This all may be a little academic anyway and I suspect you will need to find a different approach, copying that formula to nearly 3000 rows across 36 sheets is an awful lot for such a resource hungry formula - I suspect that even if you get it working then calculation time make the workbook practically un-usable.

 

[celias]

It does work!
Thank you so much, FormR!!

I guess that it was my bad. A stupid mistake: when I created the ListSheet I forgot that I had named the sheet M01 instead of M1. When I changed this, it started working.

Thank you so much!
Have a great day!

HI, I'm fairly sure that the formula supplied does what you are looking for: here is a small sample using 4 sheets - the same formula is used in column D of all sheets. If you are still having difficulties - maybe you could upload a sanitised cutdown version of your workbook to a file file sharing site (like dropbox) and share the link here.

SheetList:

Excel 2012

	A
1	M1
2	M2
3	M3
4	M4

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

M1

Excel 2012

	A	B	C	D	E
1	Name				Formula
2	A				M1
3	C				M1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

M1​

Worksheet Formulas Cell Formula E2 =INDEX(SheetList,MATCH(TRUE,INDEX(ISNUMBER(1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A2900"),$A2)),0),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To SheetList =Sheet1!$A$1:$A$4 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

M2

Excel 2012

	A	B	C	D	E
1	Name				Formula
2	A				M1
3	C				M1
4	D				M2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

M2​

M3

Excel 2012

	A	B	C	D	E
1	Name				Formula
2	A				M1
3	B				M3
4	C				M1
5	D				M2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

M3​

M4

Excel 2012

	A	B	C	D	E
1	Name				Formula
2	A				M1
3	B				M3
4	F				M4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

M4​

 

[celias]

If it isn't asking too much, and if you have time, would you mind explaining why/how the formula works?

Thank you very much, again.

 

[FormR]

Hi, glad you got it working and a little surprised that you didn't have any problems with calculation time.

Hi, one option would be to simply sort your SheetList

What I meant here was, if you sort your "SheetList" in descending order then the original formula you found would deliver the same results and will be a little more efficient.

If it isn't asking too much, and if you have time, would you mind explaining why/how the formula works?

I'll try - and I hope it helps.

Excel 2012
	A	B	C	D
1		Example based on 5 sheets where the lookup
2		value is matched in the 2nd, 3rd and 4th sheets
3
4	Step	Formula	Comment	Results
5	1	COUNTIF(INDIRECT("'"&SheetList&"'!A2:A2900"),$A2)	< This returns an array of the countifs results from each sheet in the "SheetList"	{0,1,1,1,0}
6	2	1/{0,1,1,1,0}	< This divides 1 by the result of the previous step	{#DIV/0!,1,1,1,#DIV/0!}
7	3	ISNUMBER({#DIV/0!,1,1,1,#DIV/0!})	< This converts the #DIV/0 errors to FALSE and everything else to TRUE	{FALSE,TRUE,TRUE,TRUE,FALSE}
8	4	INDEX({FALSE,TRUE,TRUE,TRUE,FALSE},0)	< This wraps the previous step an INDEX function to avoid CSE	{FALSE,TRUE,TRUE,TRUE,FALSE}
9	5	MATCH(TRUE,{FALSE,TRUE,TRUE,TRUE,FALSE},0)	< This returns the first position of the TRUE value in the array	2
10	6	=INDEX(SheetList,2)	< This index's the SheetList returning the value obtained by the previous steps
11
12		Note: Steps 2,3 could be replaced by:	{0,1,1,1,0}>0
Sheet1