Using Join/Collect to Collect from Another Sheet, Two Criteria
In Sheet 1, I want to identify the color of the title of a book that matches ISBN number, so long as the book is available in the library.
Available | BookTitle1
Unvailable | BookTitle2
Unvailable | BookTitle3
Available | BookTitle4
Available | BookTitle5
I want to look for a value (e.g., BookTitle__) and display that title if it is available.
So, for example, in Sheet 1, if I have BookTitle1, it looks in Sheet 2 for BookTitle1, and if it finds it, it displays it in Sheet 1 ONLY if it says Available in the first column. It does, so it displays "BookTitle1".
If I had BookTitle2 in Sheet 1, it would find BookTitle2 in Sheet 2; however, it would say "Not Available" in sheet 1 because the value in column 1 of Sheet 2 is "Unavailable".
If I had BooktTitle 8 in Sheet 1, i would display "Not Available" because it not even listed in column 2 of Sheet 2.
I know its INDEX/MATCH, but I do not know how to include the "Available/Unavailable" criteria when deciding to show the book title.
Help Article Resources
Check out the Formula Handbook template!