# How to connect SUMPRODUCT and INDEX formulas?

#### romek

##### New Member
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?

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### XOR LX

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

Replies
6
Views
690
Replies
14
Views
977
Replies
5
Views
402
Replies
1
Views
2K
Replies
0
Views
365

1,195,992
Messages
6,012,739
Members
441,724
Latest member
Aalbid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back