Index Match Multiple Sheets Showing NO MATCH

Jeff Reisman

I'm sorry you're having such trouble with this. I know the formula works. What you have shown above is not the formula that works. Let me see if I can explain the syntax better.

When using IF, you can specify multiple logical expressions for it to consider. Using OR tells the IF to go with the true response if at least one of the logical expressions within the OR is true. Using AND tells the IF to go with the true response if all of the logical expressions within the AND is true.

To use OR (or AND) with IF, the syntax is:

IF(OR(logical expression 1, logical expression 2, logical expression 3), value if true, value if false)

For an example, you might say IF(OR(1+1=2, 2+2=4, 4+4=10), "Correct!", "Incorrect!")

Notice that I only use OR once, with all three logical expressions inside of the OR function: OR(..., ..., ...)

In your case, your three logical expressions are:

Expression 1: COUNTIFS({GTE - PDI Offline Stock#}, [Stock #]@row, {GTE - PDI Offline Winterization}, (@cell = 1)) > 0

Expression 2: COUNTIFS({GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, [Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, (@cell = 1)) > 0

Expression 3: COUNTIFS({GTE - Motorized PDI Offline Stock #}, [Stock #]@row, {GTE - Motorized PDI Offline Winterized}, (@cell = 1)) > 0

Notice that for each, I have bold parentheses enclosing the COUNTIFS portion of the expression.

Now wrap all these inside an OR, separated by commas:

OR(COUNTIFS({GTE - PDI Offline Stock#}, [Stock #]@row, {GTE - PDI Offline Winterization}, (@cell = 1)) > 0, COUNTIFS({GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, [Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, (@cell = 1)) > 0, COUNTIFS({GTE - Motorized PDI Offline Stock #}, [Stock #]@row, {GTE - Motorized PDI Offline Winterized}, (@cell = 1)) > 0)

Now wrap the whole thing inside your IF:

=IF(OR(COUNTIFS({GTE - PDI Offline Stock#}, [Stock #]@row, {GTE - PDI Offline Winterization}, (@cell = 1)) > 0, COUNTIFS({GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, [Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, (@cell = 1)) > 0, COUNTIFS({GTE - Motorized PDI Offline Stock #}, [Stock #]@row, {GTE - Motorized PDI Offline Winterized}, (@cell = 1)) > 0), 1, 0)

If you have pasted this EXACT formula into your sheet and are still getting Incorrect Argument Set, then you need to check that your ranges inside each COUNTIFS are the same size. In other words, they should each reference the entire column, or if they are referencing a subset of rows, they need to reference the exact same rows. So {GTE - PDI Offline Stock#} should reference the entire Stock # column in GTE - PDI Offline, and {GTE - PDI Offline Winterization} should reference the entire Winterization column in GTE - PDI Offline.

Here is the a localized version of the above formula working in my test sheet:

If Student = the name in Column43 and the CheckIn box is checked, count 1; If Teacher = the name in Column43 and the TeacherCheckIn box is checked, count 1; if either of those counts is greater than zero, then check the box in CountB. In Jeff's row, one count is greater than 0, so the box gets checked. In Doug's row, both counts are greater than 0, so the box gets checked. In Jim's row, neither count is greater than 0, so the box does not get checked.