# How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

1. ## How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

Hi,

I've a data in Sheets 1 & 2 and I want the Index of Top 3 Items from Sheet 1 and Sheet 2 in each month in output/Summary Sheet.

I've provided the Sample data and output in the attached workbook.

if Possible, concatenate the Index item with the item Value in output.

Appreciate you usual help.

2. ## Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

What would the results be with duplicate value (in same sheet or different sheet but same month)?

3. Originally Posted by bebo021999
What would the results be with duplicate value (in same sheet or different sheet but same month)?
The results would be in different sheet as seen in the attached sample workbook, and the items are unique. No duplicates

4. ## Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

I mean duplicate value among items.
For instant: Item 1 = 30, Item 2=30, Item A=30
Or: Item 1=30, Item B=25, Item 3=25
Which item is 1st, 2nd and third?

5. ## Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

Attached Power Query solution

Basically I brought in your two items table, combine them as one, group them in Months calculate
their top 3 Rank ( I think if there are same values you will take Item 3 (number) instead)

then I pivot it with you desire format Item 3 x 25

Hope it helps

6. ## Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

Try with formula:

B10:
``Please Login or Register  to view this content.``
Drag down and accross

7. ## Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

@bebo021999,

output from your formula is incorrect, for the test purpose I changed the Item 1 & Item 2 values to 30 for both in Sheet 1 and Item A Value to 30 for JAN month, the output is coming for JAN as Item 1 x30, Item 1 x 30, Item 1 x 30 which wrong. It should Come as Item 1 x 30, Item 2 x30, and item A x30.

8. ## Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

Try again:

``Please Login or Register  to view this content.``
**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).

9. ## Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

Formula:
`Please Login or Register  to view this content.`

10. ## Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

Thank you all of you, @Bo_Ry, @bebo021999, @bluesky63

all three solutions works, thank you again for your support. Stay blessed.

There are currently 1 users browsing this thread. (0 members and 1 guests)