• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX MATCH and ARRAY question

kinkart

kinkart

Member
Hello,

After hours trying to understand a little bit of array to do what I wanted, I find myself stuck again with INDEX and MATCH. I'm actually not sure I'm using the right formulae :-s

So here's my setup in the file attached.

The worksheet "MASTER" is my ... welll ... master table. This is where I entered all the data I want to play with, and it is formatted as a table, not a range.

The worksheet "RANKING" is where I'd like to display a TOP 15 of the dragons that generates the most gold, per habitat.

Now, that's where it gets complicated (for me!): the possible habitats can be in MASTER column B, C, D or E
So I created a helper column, that "add" the text from each to create one word (for example B2=Water, C2=Dark, F2=WaterDark)

that column is then being searched for in RANKING to get the amount of gold IF column F contains a specific word (ie "Dark").

So far, that works. Probably not pretty (ideas to make it more elegant most welcome, especially if you can help me get rid of the array), but it gets me the correct gold value.

But I can't find a workaround to display, next to that amount, the name of the dragon. See what I've done, RANKING column B is all messed up. Something wrong with the match range I think, but no idea how to fix it.

So anyone can help, that would be much appreciated!
Oh, and if you have better ways to display a top 10, or top 15 whatever, it is most welcome too! I tried with pivot, but too much data and it slows down really badly.

Thanks in advance!
 

Attachments

Hello,

A simple MATCH always give a first position in the range, so if you have repeated values this will always give first value.

In B3, try this array formula, then copy down

=INDEX(MASTER!$A:$A,SMALL(IF(MASTERDRAGON[10]=C3,ROW(MASTERDRAGON[10])),COUNTIF(C$3:C3,C3)))

Also,

In C3, you can get same results with this array formula without HELPER column F in Master,

=LARGE(IF(MASTERDRAGON[[Habitat 1]:[Habitat 4]]=B$2,MASTERDRAGON[10]),$A3)
 
Thanks Haseeb.

the formula in C3 works, thank you, will delete my helper column now :)

However, the B3 formula doesn't work as it looks up for the first value that match, regardless of the habitat. It's an improvement (no duplicates anymore) though :)

I tried putting AND, but it doesn't work anymore:
=INDEX(MASTER!$A:$A;SMALL(IF(AND(MASTERDRAGON[10]=D3;MASTERDRAGON[[Habitat 1]:[Habitat 4]]=C$2);ROW(MASTERDRAGON[10]));COUNTIF(D$3:D3;D3)))
 
Last edited:
Nevermind, found another solution, using pivot tables.
But if anyone knows of another way to do it, without pivot or arrays, I'm still curious :-)
 
Kinkart,

In my previous post column C wasn't not looking for condition = 'Air'. Missed that one. sorry for that.

AND will not give values in Array. Use IF in your formula.

Here is one with CTRL+SHIFT+ENTER

=INDEX(MASTER!$A:$A,SMALL(IF(MASTERDRAGON[10]=C3,IF(MASTERDRAGON[[Habitat 1]:[Habitat 4]]=B$2,ROW(MASTERDRAGON[10]))),COUNTIF(C$3:C3,C3)))
 
You must log in or register to reply here.
Back
Top