Your formula is...incomplete. The logical test returns an array, but your
results are both singular answers. As such, XL is only taking the first value
from the array to determine which path to take.

If, your test is "If V1 = any of the Left(a1:IH1,4), then spit out "z", else
"x"", then the formula would be:

=IF(SUMPRODUCT(--(LEFT('Raw report'!$A1:$IH1,4)=V$1)),"z","x")

The double elipse "--" transforms true/false array into 1's and 0's, and the
SUMPRODUCT function reduces the array to a singular value. Note that this
formula does not need to be confirmed as an array function.

If this formula is actually nested in something else, it might be easier to
design the formula if we knew the entire goal. Hope it helps!
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ker_01" wrote:

Since it is related, I'll post my followup here (hoping that it is noticed
even though I've closed the thread).

It looks like I spoke too soon; after additional testing, I narrowed the
formula down to:
=IF((LEFT('Raw report'!$A1:$IH1,4)=V$1),"z","x")

which eliminates the COUNT and AND statements. It also eliminates the
'unlimited' range of the full row (1:1) in case that was a problem.

It still returns "x" (False) even though I have confirmed that there is one
(and only one) match using
V4: =Left('Raw report'!P1,4) = gives the expected value
V5: =V4=V1 = evaluates to 1 (true)
Autofilling the formula left and right shows that all other columns return 0
(false)

Once I get this working, it will be embedded in a statement that includes a
sumproduct (part of an outer IF statement) so I don't think I can use an
array formula, although it could be that my syntax attempts are just wrong

Any additional assistance would be greatly appreciated!

Best,
Keith



"ker_01" wrote:

Brilliant!

Many (many many) thanks

Keith

"Luke M" wrote:

I believe the issue is how the AND function handles arrays. It doesn't
properly multiply the two arrays to create a single array, and thus "clunks
out". Workaround is to manually force the arrays to multiply:

=IF((LEFT('Raw report'!$1:$1,4)=V$1)*('Raw report'!2:2=1),"z","x")

Now the formula is forced to create a single array composed of 1's and 0's
before moving on to the true_response and false_response portions of the IF
function.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ker_01" wrote:


I have a raw data worksheet that has similar column headers (in row 1)
identified by their first few letters; "B01_Yes", "B01_No", "B02_Yes",
"B02_No", etc.

Each row represents a single person's response to the survey. Their answer
to any question (B01, B02, etc) will show up in those cells as a 1 under the
corresponding answer column.

I'm creating a summary sheet that will just have a single column for each
question, and I want to populate the target cell with the actual answer
given. (Column headers= "B01_", "B02_", etc)

This formula correctly tells me if I the person answered just yes or no (and
not both):
=COUNT(AND(LEFT('Raw report'!1:1,4)=V$1,'Raw report'!2:2=1))
and I've confirmed that it returns a value of 1
additional info:
'Raw report'!1:1 are the headers, so taking the left 4 digits gives me the
matches of B01_(Yes) and B01_(No) when V$1 = "B01_"

However, when I wrap it with an IF statement, it returns an answer of FALSE,
in this case, returning the "x" value/
=IF(AND(LEFT('Raw report'!$1:$1,4)=V$1,'Raw report'!2:2=1),"z","x")

I'm thinking that COUNT ignores possible text or date values in my data row,
and therefore can return a simple integer, but that the IF statement maybe
can't get past non-numeric values when trying to calculate ['Raw
report'!2:2=1]

What would be a better way to evaluate whether the data row contains a 1,
that wouldn't be affected by some cells having non-numeric values?

Thank you!!
Keith