When using the IF function, how to use dynamic set for comparison in the "Logical_test" argument?

Question

I am using an array formula (in Excel 2003) to count the number of strings meeting a specific condition. I am using a column of strings instead of a table of cells to store my data because of file size limitations.

The formula is below:

{=SUM(IF((VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1))*VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),1,1)))=VLOOKUP(D2,t.lkup,2,FALSE),1,0))}

The expression VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1)) looks through the cells in a named range to return a value. This value is multiplied by another value returned by the expression VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),1,1)). The resulting product is then looked for in a set of numbers given by VLOOKUP(D2,t.lkup,2,FALSE), which returns a string like "{1,2,3,4}". If the product is an element of set, then 1 is added to the sum, else 0 is added to the sum.

When I use the array formula above (with the dynamic lookup set), a value of zero is returned. If I use the following formula,

{=SUM(IF((VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1))*VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),1,1)))={1,2,3,4},1,0))}

then the correct sum is returned. Does anyone know how to get Excel to treat the set lookup as a static set instead of a string?

Answer 1

An array formula performs multiple clalculations and returns either a single result or an array. All array arguments in the formula must be of equal size.

in your first example you compare a single value of something (as the most outer function within IF is a VALUE() function) to a VLOOKUP which returns a string ... that must go wrong ... the fact that your string contains curly brackets does not convert it into an array.

in your second example you compare a single value to an array containing elements {1,2,3,4}, so actually you do four comparisons, and if one of them resolves to TRUE you add 1.

I don't know any way to convert a comma delimited string "{1,2,3,4}" into an array {1,2,3,4} without use of VBA, but maybe you can change your tactic by converting your VALUE(...)*VALUE(...) number into a string and use the FIND() function to identify number as a substring of the condition string.

e.g. say your MID_CONCATENATE_TIMES_BLA_BLAH results in 7, and your

• condition string = "{1, 3, 5, 7, 9}", a FIND(MID_BLA, CONX_STR) = TRUE
• condition string = "{1, 2, 3, 4}", a FIND(MID_BLA, CONDX_STR) = FALSE

This will work as long as your results are 1 digit. With more than 1 digit you would need to include a SPACE before the number in both MID_BLA and CONDX_STR; also not too difficult, but adding even more complexity to the formulae.

If you want to do VBA, you can use the Split() function to create a zero-based array from a seperated string

Function StrToArray(Arg As String) As Variant
    StrToArray = Split(Arg, ",")
End Function

and surrond your VLOOKUP in (1) by StrToArray(VLOOKUP(...)), in which case you must remove the curly braces from your condition strings.

Hope that helps - good luck