Formula in a cell with Xlookups doesn't pick up new rows when added.

[KRE]

My below formula is taking billing items that are located in the range of A327:A338 and finding that billing item in a table which is located in A30:P186 and returns the value that corresponds with the Material and then mulitplies it by the quantity keyed in for the particular billing item. The formula then adds together any other billing items that also have a value that corresponds with the material.

My billing items rows account for 7 transactions which most weeks is enough but every now and then I'll have a week that has 9 billing items and I can easily add 2 rows of billing items but my formula below will not pick up the additional 2 rooms. Is there a different formula I should be using? can you use index match?

=IF(A327="","0",(XLOOKUP(A327,$A$30:$A$186,$N$30:$N$186,"")*C327)+(XLOOKUP(A328,$A$30:$A$186,$N$30:$N$186,"")*C328)+(XLOOKUP(A329,$A$30:$A$186,$N$30:$N$186,"")*C329)+(XLOOKUP(A330,$A$30:$A$186,$N$30:$N$186,"")*C330)+(XLOOKUP(A331,$A$30:$A$186,$N$30:$N$186,"")*C331)+(XLOOKUP(A332,$A$30:$A$186,$N$30:$N$186,"")*C332)+(XLOOKUP(A338,$A$30:$A$186,$N$30:$N$186,"")*C338))

 

[Cubist]

Consider making your data into a table.

 

[KRE]

Consider making your data into a table.

That didn't work. I made the data a table which I don't prefer to do but tried. I added two rows in the middle of the table and the formula didn't pick up the two rows. The problem is the formula itself I believe.

 

[Cubist]

Can you post a sample minisheet using XL2BB? Otherwise, I can't assist further.

 

[KRE]

Can you post a sample minisheet using XL2BB? Otherwise, I can't assist further.

 

[KRE]

No problem. Thanks for trying.

 

[Peter_SSs]

@KRE
The mark at the right of the posts is to mark a "solution" to your question so I have removed the mark from post #6. Please do not mark a post that doesn't contain a solution.