Complex if formulae, more than 8 ifs

QuestionEdit

I basically need to check to see if the books in my "library" are currently being rented out or not, this is for my GCSE ICT project and i need to include a complex IF formulae and i was wondering if there was a way around the 8 IFs limit on Excel. This is my current formula but it won't let me include the last 2 IFs, It is meant to be realistic and so if i had more books i would be able to replicate the formula to see if any of them are rented out, but for my project i only need 10 books and 10 people on my Spreadsheet. =IF(Fines!D2=Books!A3,"Yes",IF(Fines!D3=Books!A3,"Yes",IF(Fines!D4=Books!A3,"Yes",IF(Fines!D5=Books!A3,"Yes",IF(Fines!D6=Books!A3,"Yes",IF(Fines!D7=Books!A3,"Yes",IF(Fines!D8=Books!A3,"Yes",IF(Books!A3=Fines!D9,"Yes",IF(Fines!D10=Books!A3,"Yes",IF(Books!A3=Fines!D11,"Yes","No"))))))))))

Thanks a lot, Ian

AnswerEdit

You could use an array formula for this, something like: =IF(OR(Fines!D2:D12=Books!A3),"Yes","No") Enter it with Ctrl+Shift+Enter. This will consolidate all your IF statement in a single one that checks every cell on the range.