Amending formula to grab both portions of merged cell data

This formula grabs the resident rent for each property. It’s a beefy formula due to cells in row A and row B both being merged cells, while rows C, D, etc. are not. This is un, unfortunately how the data downloads.

The formula works, but only pulls the resident rent, not resident “other” from column C/D.

Any ideas how I could adjust the formula to pull both resident - rent and resident - other?

(I'll upload the picture of the formula and the data in a comment below)

=XLOOKUP (1, (SCAN(', Insert Collections' !$A$12: $A$9999, LAMBDA (a,b, IF (b="", a,b))) = [@[RealPage name]]) *('Insert Collections' !$B$12: $B$9999-"RESIDENT"), 'Insert Collections' !$D$12:$D$9999)

The formula is looking up the property name in column A, then finding the section in column B that is for Resident. Then returning the data in Column D. I need it to go one step further and differentiate between "rent" and "other" in column C and add both of the corresponding amounts from Column D.