MATCH/INDEX across multiple sheets with varying ranges

[himperson1]

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.

 

[himperson1]

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]

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]

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?

 

[himperson1]

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

 

[Oaktree]

I think you'd be better off adding a second column with unique ranks than trying to account for duplicates within one gargantuan formula.

For your other question about why REPT("z",255) returns the last match, you may find this chain worthwhile: How does this formula for finding the last entry in a range of text - Microsoft Community