Index/Match across multiple sheets

[jscranton]

Not sure if this can be done or not. I am trying to find the 5 largest values across all worksheets. The range is the same for each worksheet. I want to return those values (the 5 largest) as well as some other values from the related ranges.

I can use the LARGE function to return the largest value across the range of worksheets (A2 = 1 which returns the largest value across B:B on all worksheets between Sheet2:Sheet3)

=LARGE(Sheet2:Sheet3!B:B,A2)

Now I need to LOOKUP the value returned by the LARGE function and return the other values I need. I thought this would work but it does not:

=MATCH(B2,Sheet2:Sheet3!B:B,0)

I thought if I could match the value returned by the LARGE function, I could use INDEX to return the other values I need but this doesn't work.

Any thoughts?

J

 

[Aladin Akyurek]

Largest values for whom or what? You need to specify what you have in e.g. Sheet2.

 

[jscranton]

Aladin,

In column "B" of each worksheet of my workbook are numbers. I can use the LARGE function to return the top 5 largest values from all the worksheets. what I haven't figured out is how to return associated values from those five largest values.

For example,

from a range across multiple sheets (Sheet2:Sheet5!B:B), the largest value found within that range is "62" which is found on Sheet3!$B$36. The large function works fine to return me the "62". But, what I now need, for example, is the value found on Sheet3!$A$36. I assumed Index(Match) would get me there but doesn't seem to work across multiple sheets.

I should add that I know vba solves this pretty easily but I am hoping to avoid a macro solution given who the end user is.

 

[Aladin Akyurek]

No need to repeat yourself. Just specify the ranges of Sheet2. B2:B100 houses apparently numbers. How about those "associated values"? Are they secret?

 

[jscranton]

Basically, there are three columns of data on each sheet (except for the first sheet):

	Company	Count	Location
1	zComp	62	Florida
2	yComp	58	Alabama

<tbody>
</tbody>

My goal is that once I locate the highest count from all worksheets in column B, I want to be able to return the value in Column A & C for that row on that worksheet.

So, I can return the 62 but I can't figure out how to also return the zComp and Florida.

Thanks,

J

 

[Aladin Akyurek]

Do these companies repeat in the same sheet?

Do these companies repeat across sheets?

If they do, must top 5 determined on the basis of individual counts or totalized counts.

 

[jscranton]

They are unique to each sheet. Not based on total count (since they unique). In other words, a company on sheet 2 only appears on sheet 2 and will not appear anywhere else.

 

[jscranton]

Still haven't figured out how to do this. Any other suggestions?

 

[XOR LX]

Hi.

Assuming the range containing numerics in each sheet is B1:B10ǂǂ, first go to Name Manager and define:

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

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

Name: Arry1
Refers to: =ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS($B$1:$B$10)))

Name: Arry2
Refers to: =COLUMN(INDEX($1:$1,1):INDEX($1:$1,COUNTA(Sheets)))

Then enter this array formula** in your first cell of choice, which will return the value from the range A1:A10 across all sheets which corresponds to the largest value found within the range B1:B10 across all sheets:

=INDEX(INDIRECT("'"&INDEX(Sheets,MIN(IF(N(OFFSET(INDIRECT("'"&Sheets&"'!B1"),Arry1-1,))+Arry2/10^3+Arry1/10^6=LARGE(N(OFFSET(INDIRECT("'"&Sheets&"'!B1"),Arry1-1,))+Arry2/10^3+Arry1/10^6,ROWS($1:1)),Arry2)))&"'!A1:A10"),MIN(IF(N(OFFSET(INDIRECT("'"&Sheets&"'!B1"),Arry1-1,))+Arry2/10^3+Arry1/10^6=LARGE(N(OFFSET(INDIRECT("'"&Sheets&"'!B1"),Arry1-1,))+Arry2/10^3+Arry1/10^6,ROWS($1:1)),Arry1)))

Copy down to give the values corresponding to the second, third, etc. largest values.

I have assumed that every entry within the range B1:B10 across all sheets is numeric (i.e. also non-empty). Also, we can simplify somewhat this set-up if you can guarantee that all numerics are unique across all sheets. Otherwise, we require some means of differentiating identical amounts; hence the addition of very small, yet unique fractions to each numeric within the set-up I posted.

Regards

ǂǂ If you wish to extend this set-up to work beyond row 10, the parts to be changed are highlighted in red in the above formulas.

** Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[Aladin Akyurek]

Still haven't figured out how to do this. Any other suggestions?

If you already have a list of the companies which figure across your sheets, a different approach would be also possible.

The approach which follows uses a UDF that I often invoke: ARRAYUNION.

Sheet2

Row\Col	A​	B​	C​
1​	Company	Count	Location
2​	apple	62​	California
3​	google	58​	California
4​	facebook	62​	California
5​	tomtom	70​	NL
6​	nokia	65​	Finland

Sheet3

Row\Col	A​	B​	C​
1​	Company	Count	Location
2​	vestel	62​	TR
3​	beko	58​	TR
4​	franz inc	62​	California
5​	trigon	70​	NL
6​	saab	65​	Sweden

Sheet4

Row\Col	A​	B​	C​
1​	Company	Count	Location
2​	zComp	62​	Florida
3​	yComp	65​	Alabama
4​	ibm	62​	New York
5​	intel	70​	California

Add the following code to your workbook, using Alt + F11:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
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

Define ComRange (i.e. the company range) in Formulas | Name Manager as referring to:

<strike></strike>=arrayunion(Sheet2!$A$2:$A$6,Sheet3!$A$2:$A$6,Sheet4!$A$2:$A$5)

Define CountRange as referring to:

=arrayunion(Sheet2!$B$2:$B$6,Sheet3!$B$2:$B$6,Sheet4!$B$2:$B$5)

Define LocationRange as referring to:

=arrayunion(Sheet2!$C$2:$C$6,Sheet3!$C$2:$C$6,Sheet4!$C$2:$C$5)

Finally, define Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(ComRange)))

Sheet1 (Top N results sheet)

Row\Col	A​	B​	C​
1​	top	5​
2​	top adjusted	6​
3​
4​	top scores	top companies	Location
5​	70​	tomtom	NL
6​	70​	trigon	NL
7​	70​	intel	California
8​	65​	nokia	Finland
9​	65​	saab	Sweden
10​	65​	yComp	Alabama
11​

In B2 control+shift+enter, not just enter:

=SUM(IF(CountRange>=LARGE(CountRange,MIN(B1,COUNT(CountRange))),1))

In A5 just enter and copy down:

=IF(ROWS($A$5:A5)<=$B$2,LARGE(CountRange,ROWS($A$5:A5)),"")

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

=IF($A5="","",INDEX(ComRange,SMALL(IF(CountRange=$A5,TRANSPOSE(Ivec)),
    SUM(IF($A$5:A5=A5,1)))))

In C5 control+shift+enter and copy down:

=IF($A5="","",INDEX(LocationRange,SMALL(IF(CountRange=$A5,TRANSPOSE(Ivec)),
    SUM(IF($A$5:A5=A5,1)))))