Using Join/Collect to Collect from Another Sheet, Two Criteria

Art Schneiderheinze

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.

Sheet 2

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.

Paul Newcome

You could actually use an IF statement for this. Basically the only way to return "Available is if the title is available. The other two possibilities would return "Unavailable".

 

So give something like this a try...

 

=IF(COUNTIFS({Other Sheet Book Title Column}, [Book Title]@row, {Other Sheet Availability Column}, "Available") > 0, "Available", "Unavailable")

.

Basically we count how many times that book's title and the word "Available" are in the same row. If that number is greater than 0 meaning that there is a row that meets both requirements, then we show it as "Available", else "Unavailable".

Art Schneiderheinze

Logically, I totally get this. I tried it, and I am getting a #CIRCULAR REFERENCE error. Both are separate columns in the first sheet:

=IF(COUNTIFS({CMRED-D2LMasters}, [D2L Master]@row, {CMRED-DevStatus}, "No Master") > 0, [D2L Master]@row, "No Master")

 

{CMRED-D2LMasters} is the column with the names of the course master in Sheet 1

[D2L Master]@row is the cell with the course master name in consideration in Sheet 2 (where I wrote this formula)

{CMRED-DevStatus} is the column with the development status of the course master in Sheet 1 (it is either "Completed", "In Review", "In Progress", or "No Master").

 

Whether or not the master listed in Sheet 2 is listed in Sheet 1 or not (or if the status is or is not "No Master"), I get the #CIRCULAR REFERENCE error. I do not have any error in Sheet 1 in these columns.

Paul Newcome

"[D2L Master]@row is the cell ....... (where I wrote this formula)"

 

Referencing the cell the formula is in is where the circular reference comes from.

 

Try putting the formula in a different column. Use this second column for the display column and hide the [D2L Master] column.