CODEWITHSUNDEEP
excel
rjbogz
rjbogz

Reputation: 870

Can't get INDEX/MATCH functions to do what I need?

It is quite an in depth excel sheet (to me) so here is a link to it: https://dl.dropboxusercontent.com/u/19122839/Movies.xlsm

On the Filters sheet, I have a search feature. This allows you to put in different genres, years, etc. and will pull up results.

The genre part does not seem to be working correctly for some reason.

In the movie_genres sheet, there is a Genre Equals and Genre Count column that seem to be marking the information correctly, but when you go to the movies sheet, the Matches Genre column does not. I use this function:

=INDEX(Genres[Genre Count],MATCH(Movies[[#This Row],[ID]],Genres[ID],0))

Which, to me, should pull the Genre Count, but in the case where there are more than one genre (I used Blank Check as an example in this case), it doesn't mark it as a 1. How can I make it so that this gets corrected.

For example, if you add the Comedy as a second genre, it pulls up more results than if you only have Family. I think I just need a fresh pair of eyes looking at this and it is probably something dumb, but any help would be great.

I believe I need to make it so that the index/match function I use in Movies[Matches Genre] will work as long as there is a 1 in Genres[Genre Count] for that ID. It only seems to work if there is a 1 in the first instance of the ID.

EDIT: I have added in a COUNT feature to better explain what I am talking about. With only Family as a genre, it shows there are 10 results, but when you add Comedy as a second genre, you get 40 results. This number should never go up as you add genres.

Upvotes: 0

Views: 106

Answers (1)

barry houdini
barry houdini

Reputation: 46331

Perhaps try using SUMIF like this

=SUMIF(Genres[ID],[@ID],Genres[Genre Count])

If one movie might have several 1s but you only want 1 maximum then change to

=IF(SUMIF(Genres[ID],[@ID],Genres[Genre Count])>0,1,0)

Upvotes: 1

Related Questions