How to connect SUMPRODUCT and INDEX formulas?

[romek]

Hi, thanks to Xor Lx's support we have created a formula:

=SUMPRODUCT(N(VLOOKUP(T(IF({1},Sheet1!D2:D199)),Sheet2!A2:N199,14,0)<>Sheet1!R2:R199))

Thas works great, however only for defined range - only if there is 199 records, and when I extend the are for example to D200, this results in N/A error because there is blank cell.

So, we need to also INDEX function to find reference to last nonblank cell and sample formula for this is =INDEX($A:$A,2):INDEX($A:$A,MAX(MATCH(9.9E+307,$A:$A),MATCH(REPT("z",255),$A:$A)))

However, I am not able to connect this two formulas to make it work. Could you please help?

 

[XOR LX]

Hi again.

It's best if you define each of the ranges via Name Manager (Formulas tab), giving each an appropriate name. For example, Sheet1!D2:D199 would become:

=INDEX(Sheet1!$D:$D,2):INDEX(Sheet1!$D:$D,MAX(MATCH(9.9E+307,Sheet1!$D:$D),MATCH(REPT("z",255),Sheet1!$D:$D)))

As I mentioned previously, this definition will fail if any of the blank cells at the end of those ranges are not "genuinely" blank, but e.g. contain the null string ("") as a result of formulas in those cells. If that is the case let me know and we can look at a different definition for the dynamic range which accounts for such entries.

Regards

 

[romek]

Is the Index function instead of the first Sumproduct function? I am not able to connect them together to make it all work

 

[XOR LX]

No. The last formula I gave simply defines the range dynamically, and should be stored within Name Manager with an appropriate name.

After that, you simply replace the static range(s) in the VLOOKUP formula with these Names.

Regards