Looking for value across multiple arrays on multiple tabs

[caspito]

Hello all, I'm new around here and already you guys have been awesome with helping me get better at Excel. I am learning a lot and am very grateful for this forum and its members.

So, I have a column of ten values on a tab called HOF in Cells B51:B60. Each of these values represents the ten largest values within four arrays, each on a different tab. The tabs are called 2010, 2011, 2012, 2013, and 2014 and each array occupies B16:N27 on its respective tab. Column A on each tab, specifically Cells A16:A27, lists names of individuals who achieved these values.

I am searching for a lookup formula that can plumb the depths of all five tabs (2010 to 2014) to retrieve the name of the individual from Column A in whose row there is contained the particular value from the HOF tab's Cells B51:B60.

I thought I got close by discovering INDIRECT... Am I on the right track?

Thanks!

 

[Eric W]

Well, you'd think so! I've spent a lot of time trying to get array functions to work with lookup functions, which is kind of what you're looking at with INDIRECT. But I've never managed to get them to work. (Maybe one of the more experienced folks here has a solution.) Even if there is a way, I know that the CONCATENATE function does not work with arrays.

All is not lost though. Since you only need to look at 5 sheets, I created an array function to do a lookup on 2010, then repeated it 4 more times. This makes it fairly long and ugly, but it does work. Since this is an array formula, enter it with Control-Shift-Enter. I assume you plan to put it in A51 on HOF, or maybe C51 on HOF. Anything on row 51 is OK. Then copy it down to row 60.

=IFERROR(INDEX('2010'!$A$16:$A$27,MAX(IF(B51='2010'!$B$16:$N$27,ROW($B$16:$N$27)-ROW($A$16)+1,-999))),"")&
IFERROR(INDEX('2011'!$A$16:$A$27,MAX(IF(B51='2011'!$B$16:$N$27,ROW($B$16:$N$27)-ROW($A$16)+1,-999))),"")&
IFERROR(INDEX('2012'!$A$16:$A$27,MAX(IF(B51='2012'!$B$16:$N$27,ROW($B$16:$N$27)-ROW($A$16)+1,-999))),"")&
IFERROR(INDEX('2013'!$A$16:$A$27,MAX(IF(B51='2013'!$B$16:$N$27,ROW($B$16:$N$27)-ROW($A$16)+1,-999))),"")&
IFERROR(INDEX('2014'!$A$16:$A$27,MAX(IF(B51='2014'!$B$16:$N$27,ROW($B$16:$N$27)-ROW($A$16)+1,-999))),"")

Let me know how it works.

 

[Peter_SSs]

1. What is to happen if multiple names on a single tab and/or multiple names on multiple tabs all have the value that appears on the HOF sheet?

2. You mentioned "four arrays, each on a different tab". Did you mean 5 arrays?

3. Are the names in A16:A27 the same on each tab & in the same order?

 

[caspito]

Thanks, Eric! It worked!

Also, Peter, thank you for bringing up these necessary clarifications:

1. I hadn't thought of that... I guess if there is a matching value -- they go to the hundredth, so this is a low-probability event. But a not zero-probability event, so I guess I will cross that bridge when and if I ever do get there!

2. Yes, I meant 5.

3. Yes, they are the same every year and in the same order.

Thanks again everyone who helped me!

Best,

Joe

 

[Eric W]

I'm happy it worked! Thanks for the feedback.

 

[caspito]

I'm happy it worked! Thanks for the feedback.

Is it possible to modify the formula to display the value of the "header" cell on Row 15 Columns B through N? The "header" cell displays Week 1, Week 2, all through Week 13.

I want to display (next to the individual from Column A's name) the week in which that value was achieved.

Also, next to that, I want to display the name of the tab from which the high value came...

I'm sure its just a few adjustments to the original formula, right? To get the "header" column name (the week), I tried switching ROW to COLUMN and switching +1 with -999, but no dice. Am I hot or cold?

Thanks!

 

[Eric W]

You had the right idea! This will give you the header cell, put this formula in D51 and copy it down:

=IFERROR(INDEX('2010'!$B$15:$N$15,MAX(IF(B51='2010'!$B$16:$N$27,COLUMN($B$16:$N$27)-COLUMN($B$16)+1,-999))),"")&
IFERROR(INDEX('2011'!$B$15:$N$15,MAX(IF(B51='2011'!$B$16:$N$27,COLUMN($B$16:$N$27)-COLUMN($B$16)+1,-999))),"")&
IFERROR(INDEX('2012'!$B$15:$N$15,MAX(IF(B51='2012'!$B$16:$N$27,COLUMN($B$16:$N$27)-COLUMN($B$16)+1,-999))),"")&
IFERROR(INDEX('2013'!$B$15:$N$15,MAX(IF(B51='2013'!$B$16:$N$27,COLUMN($B$16:$N$27)-COLUMN($B$16)+1,-999))),"")&
IFERROR(INDEX('2014'!$B$15:$N$15,MAX(IF(B51='2014'!$B$16:$N$27,COLUMN($B$16:$N$27)-COLUMN($B$16)+1,-999))),"")

The one for the sheet name is a bit easier:

=IF(MAX(IF(B51='2010'!$B$16:$N$26,1,0))=1,"2010","")&
IF(MAX(IF(B51='2011'!$B$16:$N$26,1,0))=1,"2011","")&
IF(MAX(IF(B51='2012'!$B$16:$N$26,1,0))=1,"2012","")&
IF(MAX(IF(B51='2013'!$B$16:$N$26,1,0))=1,"2013","")&
IF(MAX(IF(B51='2014'!$B$16:$N$26,1,0))=1,"2014","")

Here's how the "header" formula works, so you can see what's going on. I'll just look at the top line (2010), the rest work the same.

Consider this formula: IF(B51='2010'!$B$16,COLUMN($B$16)-COLUMN($B$16)+1,-999) and let's say it is not true. Then it will return -999 as a result.

Now consider: IF(B51='2010'!$C$16,COLUMN($C$16)-COLUMN($B$16)+1,-999) and let's say it is true. Then it will return COLUMN($C$16)-COLUMN($B$16)+1, or 3-2+1 = 2, which is the column offset into the range.

Here's where the array part kicks in. If you write the formula like IF(B51='2010'!$B$16:$N$27,COLUMN($B$16:$N$27)-COLUMN($B$16)+1,-999) and tell Excel that's it's an array formula by typing Control-Shift-Enter, then Excel will look for the range in the formula ($B$16:$N$27) and calculate the result for every cell in the range and return all the results in an array, like {-999, 2, -999, -999, -999, . . . -999}. According to what you said, there should only be 1 match, so there will be 1 value that's a column offset, and the rest will be -999. Now we have to compress the array down to 1 value. The MAX function will pull the value 2 from that array.

Now this function: INDEX('2010'!$B$15:$N$15, 2) pulls the second value from the $B$15:$N$15 range, or "Week 2".

If on Sheet 2011, the value in B51 is not found at all, then the array will be all -999, and the max of the array will be -999. Then we'll end up trying INDEX('2011'!$B$15:$N$15, -999). This will create an error, which is why I wrapped the whole thing in IFERROR. If there's not an error, like "Week 2", it returns "Week 2", if there is an error, it returns "". Then finally, the & operator concatenates all five results together.

Given that explanation, go back to the first formula and see what you needed to change.

I'll leave the second formula for you to figure out as an exercise for the reader (I've always wanted to say that!).

 

[Peter_SSs]

If the 5 'year' sheets have identical layouts (& this will always be the case) including names in A16:A27 and week labels in B15:N15 and that you want to report the sheet name where the value occurs, then you could try these formulas which don't require array entry but one of them does require Excel 2010 or later.

Also pointing out that INDIRECT is a volatile function so keep that in mind if your workbook's performance deteriorates.

You say you don't expect there to be more than one occurrence of one of the maximum values. If that does occur, the way my formulas are structured, only one will be reported and it will be the one on the 'earliest year' sheet & if multiples occur on that sheet then it will use the 'earliest week' and if multiples occur in that year and week then the 'earliest row' will be used. The value 2000 on the '2010' sheet demonstrates this.

Showing the 2010 sheet only for the year sheets for ref by the previous paragraph.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
15		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13
16	Name 1	2	14	62	4	90	34	46	81	92	46	62		11
17	Name 2	84	33	55	2	2000	19	19	38	53	51	4	23	94
18	Name 3	3	84	12	2000	10	2	85	98	80	65	49	100	54
19	Name 4	56	39	2000	6	6	6	85	47	82	54	25	78	57
20	Name 5	71	2000	66	6	6	6	6	18	37	67	84	78	6
21	Name 6	30	13	54	6	6	6	55	63	27	59	81	54	6
22	Name 7	60	46	83	53	25	76	98	64	94	36	57	25	55
23	Name 8	81	2000	25	40	19	1	87	81	9000	96	81	38	20
24	Name 9	63	71	28	84	100	28	2000	51	33	37	31	20	37
25	Name 10	80	15	25	42	63	83	86	85	93	11	53	86	20
26	Name 11	55	48	69	24	46	30	48	32	85	40	3	40	32
27	Name 12	35	57	24	4	96	3	26	88	49	69	79	34	69
2010

Each formula below is copied down.

Excel Workbook
	B	C	D	E
50	Top 10	Name	Week	Sheet
51	10000	Name 3	Week 5	2014
52	9000	Name 8	Week 9	2010
53	8000	Name 11	Week 3	2012
54	7000	Name 1	Week 8	2012
55	6000	Name 1	Week 11	2012
56	5000	Name 7	Week 11	2011
57	4000	Name 4	Week 2	2013
58	3000	Name 8	Week 8	2014
59	2000	Name 5	Week 2	2010
60	1000	Name 10	Week 7	2011
HOF

 

[Eric W]

Wow, Peter! Very clever. I've spent some time deconstructing your formulas, and they definitely show that you have a lot of experience.

These will go into my notes.