Hello everyone!
This might be really easy (it certainly seems like it should be) but I've been trying to work it out for days with only limited success so here goes...
I'm making a spreadsheet to do with films that I have seen (because I am cool...) and one of the things that I would like to do is set it up so that when I add a film to the list of those that I have seen, "Yes" appears next to this film in other lists in the spreadsheet (Oscar winners, etc). I have found a way to do this for just the title of the movie with a formula like this for the example table below:
=IF(COUNTIF(A1:A6, "Movie Title"),"Yes","No")
This would be fine if there was only one movie with each name but unfortunately with remakes and whatnot that's not the case. So, what I'm looking for is a formula that gives a "Yes"/"No" based not just on the title but on the director and, preferably also the year (as some directors have remade their own movies and so on). So in the example below, I'd like a formula that will acknowledge that I have seen David Cronenberg's 'Crash', but would not also acknowledge Paul Haggis's 'Crash', ie. "Yes" when 'Crash' is in column A AND 'David Cronenberg' is in the same row in column B. If that makes sense. I hope it does!
[A] [B] [C]
[1] Batman Returns Tim Burton 1992
[2] Crash David Cronenberg 1996
[3] As Good As It Gets James L. Brooks 1997
[4] 12 Monkeys Terry Gilliam 1995
[5] Crash Paul Haggis 2004
[6] Scott Pilgrim Vs The World Edgar Wright 2010
I'm not sure if this would be a VLOOKUP or what; I feel it should be quite easy but I'm totally stumped!
Thanks in advance to anyone who can help me!!!
PS- I'm very sorry if this question has already been asked but I can't find it anywhere...
Bookmarks