Looking up multiple columns in a nestled IF

[zalx]

Hey,

Very amateur Excel user here, so please excuse me if I am calling things by the wrong name - until a couple of months ago I hadn't used Excel for anything much at all so this is all a learning process. Have spent hours on this, and am lost in a sea of errors so that I don't know what is wrong any more.

I keep a spreadsheet for films I have watched, and want to return some data on them, so I have sheets for film by Director and by Actor. I have managed to organise the Director sheet to display my top rated film for each Director by using the following formula, where Film is the sheet holding the data, C the column with the name of the film, D the column with the name of the director, T the score, and A3 being the reference on my Directors sheet for the director I want to look up:

=INDEX(Film!$C$2:$C$20000,MATCH(MAX(IF(Film!$D$2:$D$20000=A3,Film!$T$2:$T$20000)),IF(Film!$D$2:$D$20000=A3,Film!$T$2:$T$20000),0))

This works perfectly, but I can't find out a suitable way to do it for Actors, as I am trying to search through multiple possibilities instead of just one Director . I have a column with all main actors in a film listed separated with a comma and a space, but as I understand it through hours of Googling, IF cannot deal with wildcards, so I cannot use that to refer to. I have tried using a set of columns where I can list each actor individually, and then change my reference to refer to all of them - instead of Film!D2:D20000 it becomes Film!AA2:AZ20000 - but that returns #N/A. I have tried setting up a named range for the same set of data, but that does the same thing.

I feel like the answer must be in some form of the formula above, but I just can't even work out why the things I have tried don't work, let alone how I can correct that. Am I missing something incredibly obvious, or do I need to use a totally new function? Thank you in advance.

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[zalx]

Of course, thank you.

This is the spreadsheet I am using. The column I am having difficulty with is F on the Actors sheet, and the similar idea I have working is in the Directors sheet. There are a handful of my attempts in that column still so I could keep an eye on what I had tried.

Please excuse my potentially poor taste in films...

[Glenn Kennedy]

Try this in f2, copied down:

=IF(A2="","",IFERROR(INDEX(Film!C:C,AGGREGATE(15,6,ROW(Film!$C$2:$C$13)/((ISNUMBER(SEARCH(Actors!$A2,Film!$H$2:$H$13)))*(Film!$T$2:$T$13=Actors!G2)),1)),""))

[zalx]

That works an absolute treat! Thank you so much! I can use it now and will try and work out why it works :-p You have made my day - thank you.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[zalx]

Done and done. Thanks again.