Assistance needed with Index/Match to display a list

So basically, I have a file that lists characters of a game. Some of the characters are a different variation of one main character, they are from different areas of the game, have different roles, and so on. I have named ranges and tables to define different attributes for each character as defined in the game. These include the main type of character, variations of that character, their role in the game, type of character they are, etc. I am trying to make a filter list for all the characters based on various criteria. For example, if I wanted to search by one specific character type (e.g. attacker), then by rarity (e.g. rare), I have dependent drop down lists to define what I want to search for. My problem comes when I try to build the Index/Match formula to display all the results of that particular search. I can't seem to wrap my head around how to get it to display the rows of the table I want with the search criteria provided in the drop downs.

If I search by the character, I want to list all the variations of that character. If i choose Role, I want to list all the types of say attackers. If i choose Region, I want all characters from that region listed.

I have attached the file I am currently working on.

Hi ,

If you do not mind using Excel's Advanced Filter feature , it is quite easy.

Just click on Advanced under the Data menu heading , click on Copy to another location , and in the List Range data entry dialog box , enter :

ToonTable

This is a named range referring to the ToonListTable.

The other data entry items can remain once they have been set.

Excel will display a message asking you whether the destination cells can be overwritten ; click on Yes.

That is all.

Narayan

Was looking for an actual formula. Ive been trying to teach myself the index match formulas and I know they're very powerful. I just haven't been able to get the results I need. I know it's possible to do it.

Hi ,

I am not able to post the formula because of the website firewall.

See the attached file.

Narayan

Thank you Narayan! That was exactly what I needed. Very much appreciated (: