# MATCH/INDEX across multiple sheets with varying ranges

#### himperson1

##### New Member
hello all,
i have a set of data within varying ranges on different pages (ie: data on sheet1 is within d18:d28, but similar data on sheet2 is within d24:d50, and on sheet3 within d30:d35)
i have already managed to find the largest 3 values among this range, but now i am trying to perform an index/match to find the accompanying label on column c (sheet1 c24:c50, etc).

i have tried to use http://www.mrexcel.com/forum/excel-questions/357517-large-function-across-multiple-worksheets.html as a resource, but their function operates when the sheets all use the same range.

im utterly stuck. would anybody be able to adapt this for my needs?

thanks.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### himperson1

##### New Member
for clarification: "i have already managed to find the largest 3 values among this range"
the largest 3 values among all 3 ranges on the different pages. so if sheet1 has 1,2,50; sheet2 has 4,5,10; and sheet3 has 9,20,25; i have already found 50,25,20 and am now looking for the corresponding labels for each from sheet1, sheet3, sheet3.

thanks again.

#### Oaktree

##### MrExcel MVP
You could try the LOOKUP(REPT("z",255),CHOOSE({1,2,3},INDEX(MATCH(...)),INDEX(MATCH(...)),INDEX(MATCH(...)))) approach...

Where A1 is the value you're trying to find, try this:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},INDEX(Sheet1!C18:C28,MATCH(A1,Sheet1!D18:D28,0)),INDEX(Sheet2!C24:C50,MATCH(A1,Sheet2!D24:D50,0)),INDEX(Sheet3!C30:C35,MATCH(A1,Sheet3!D30:D35,0))))

#### himperson1

##### New Member
You could try the LOOKUP(REPT("z",255),CHOOSE({1,2,3},INDEX(MATCH(...)),INDEX(MATCH(...)),INDEX(MATCH(...)))) approach...

Where A1 is the value you're trying to find, try this:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},INDEX(Sheet1!C18:C28,MATCH(A1,Sheet1!D18:D28,0)),INDEX(Sheet2!C24:C50,MATCH(A1,Sheet2!D24:D50,0)),INDEX(Sheet3!C30:C35,MATCH(A1,Sheet3!D30:D35,0))))

thank you for the help, it worked fantastically.

for my own learning purposes, what is REPT doing here?

Last edited:

#### himperson1

##### New Member
i found an edge case in which the formula given does not behave as desired:
LARGE will correctly factor in ties if i allow it to search for top 1 and then top 2 results.
what edit to the given formula would i need in order for the index/match to behave in the same manner? at current it is simply spitting out the 1st instance of the largest value twice.

ex: my pool of data has 50, 25, 100, 100, 75 which correspond to a, b, c, d, e respectively.
large 1,2,3 will give me 100, 100, 75 respectively.
formula will give d,d,e instead of desired c, d, e or d, c, e

Last edited:

Replies
0
Views
484
Replies
6
Views
169
Replies
3
Views
210
Replies
9
Views
423
Replies
3
Views
328

Threads
1,191,395
Messages
5,986,357
Members
440,019
Latest member
Natalie12138

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back