INDEX, MATCH, MAX, function for multiple data sheets

romesk · Aug 12, 2015

Take the following scenario. I must look through 2 similar columns in two separate sheets. I must search the two columns and return the max value in a cover page: This can be completed using MAX(Sheet1!A1:A2,Sheet2!A1:A2) etc.

The harder part that I can not seem to find a solution for:

I must return the row label for the corresponding max value in another cell on the cover page. So I believe an Index,Match,Max function can accomplish this. EXAMPLE:

SHEET 2: NAME	TEST 1
James	90
Lisa	73
Frank	62
Bob	60
Ann	76
Matt	44

(This data for example is from School A)

Sheet 3:

NAME	TEST 1
Sarah	86
William	88
Andy	82
Ross	78
Brenda	76
Jason	81

(This data, for example is from School B)

Cover Page (Desired Result from the data Above)

		Name	Grade
Highest Grade for Test 1 :	James	90

Grade as mentioned previously can be obtained using MAX(....both sheet columns).
How would I be able to return the Name? I have tried the following formula previously in the cover page cell which only worked if searching through one sheet:
=INDEX(Sheet2!$A$2:$A$7,MATCH(MAX(SHEET2!B2:B7),Sheet2!B2:B7,0))

Any help would be appreciated, Thank you!

ALSO: (I would need a way for the function to be able to post multiple names if the max grade occurs more than once).

XOR LX · Aug 12, 2015

Hi.

Will this only ever be for two sheets, or do you require a generic solution which will work for potentially more?

I only ask as a tailored solution for just two sheets may prove to be more efficient than the generic, multi-sheet set-up.

Regards

romesk · Aug 12, 2015

Indeed, more sheets may come into play. Sheets may be added over time, so perhaps a generic solution would be appropriate. However, would this generic solution differ greatly from a solution that would just need perform the operation necessary for the example mentioned above (just two sheets)?

XOR LX · Aug 12, 2015

romesk said:
However, would this generic solution differ greatly from a solution that would just need perform the operation necessary for the example mentioned above (just two sheets)?

Probably, yes.

Which do you require?

Regards

romesk · Aug 12, 2015

XOR LX said:
Probably, yes.

Which do you require?

Regards

Both would be ideal if possible, considering that I am,in fact, uncertain about the addition of more sheets. However, a generic solution that would apply for more sheets seems to be reasonable. Thank you! (It must be a solution for 2+ sheets)

Aladin Akyurek · Aug 12, 2015

1. If you would normalize your data, you would have the required data on a single sheet. Such allows for easier and more efficient processing.

2. What follows would be more efficient if we had ARRAYUNION as a native function.

Sheet2 (First source)

Row\Col	A	B
1	NAME	TEST 1
2	James	90
3	Lisa	73
4	Frank	62
5	Bob	60
6	Ann	76
7	Matt	44

Sheet3 (Second source)

Row\Col	A	B
1	NAME	TEST 1
2	Sarah	86
3	William	88
4	Andy	82
5	Ross	90
6	Brenda	76
7	Jason	81

cover houses processing...

Row\Col	A	B
1		grade
2	TEST 1	90
3		2
4		name(s)
5		James
6		Sarah
7

Activate Formulas | Name Manager and define the following named ranges...

name as referring to:

Rich (BB code):


=TRANSPOSE(arrayunion(Sheet2!$A$2:$A$7,Sheet3!$A$2:$A$7))

test1 as referring to:

Rich (BB code):


=TRANSPOSE(arrayunion(Sheet2!$B$2:$B$7,Sheet2!$B$2:$B$7))

Ivec as referring to:

Rich (BB code):


=ROW(INDIRECT("1:"&ROWS(name)))

Note. The VBA code for arrayunion is given at the end.

B2, just enter:

Rich (BB code):


=MAX(test1)

B3, control+shift+enter, not just enter:

Rich (BB code):


=SUM(IF(test1=B2,1))

B5, control+shift+enter, not just enter, and copy down:

Rich (BB code):


=IF(ROWS($B$5:B5)<=$B$3,INDEX(name,SMALL(IF(test1=$B$2,Ivec),ROWS($B$5:B5))),"")

For the foregoing set up to work, you need to add the code for ARRAYUNION to your workbook as a module, using Alt+F11...

 [FONT=&quot]Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: http://makeashorterlink.com/?P20022174
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function[/FONT]

romesk · Aug 12, 2015

It would be beneficial if the solution to the example above was solved by using primarily functions an equations, if not all of it. Introducing macros and VBA may complicate things for end users.

Aladin Akyurek · Aug 12, 2015

romesk said:
It would be beneficial if the solution to the example above was solved by using primarily functions an equations, if not all of it. Introducing macros and VBA may complicate things for end users.

Not everything is VBA, just one function...

XOR LX · Aug 13, 2015

I would normally provide a generic solution which is sheet-independent. However, I have just discovered an alternative, shorter solution which will work provided the sheets in question are:

1) Consecutive within the workbook
2) All named as "SheetX", where X is some integer

Then, if you first go to Name Manager and define:

Name: Sheets
Refers to: ={"Sheet2","Sheet3","Sheet4","Sheet5"}

(Or whatever happen to be the sheet names in question.)

Then:

=INDIRECT(SUBSTITUTE("Sheet"&AGGREGATE(15,6,(SUBSTITUTE(Sheets,"Sheet","")+ROW(B$2:B$7)/10^3)/(N(OFFSET(INDIRECT("'"&Sheets&"'!B2"),ROW(B$2:B$7)-MIN(ROW(B$2:B$7)),))=MAX(Sheet2:Sheet5!B$2:B$7)),ROWS($1:1)),".0","!R")&"C1",0)

and copied down.

The 2 and 5 in the portion:

Sheet2:Sheet5!B$2:B$7

should be amended to match your first and last sheets accordingly.

Regards

icefalcon · May 1, 2018

INDEX, MATCH, MAX, function for multiple data sheets

romesk

New Member

Excel Facts

XOR LX

Well-known Member

romesk

New Member

XOR LX

Well-known Member

romesk

New Member

Aladin Akyurek

MrExcel MVP

romesk

New Member

Aladin Akyurek

MrExcel MVP

XOR LX

Well-known Member

icefalcon

New Member

Similar threads

Forum statistics

Share this page

INDEX, MATCH, MAX, function for multiple data sheets

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

MrExcel MVP

New Member

MrExcel MVP

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock