XLOOKUP with partial match or working relative cell reference

[mummo]

Hi,

I am working with data table of loans and I have some problem with loans that have several related loans.
I will try my best to explain with the attached example image for reference.

Usually a loan has none or 1 related loans, for analysis purposes I need to have the amount of all related loans on the same row. For these normal cases a regular xlookup works fine, but the problem is when there are several related loans and I need the sum of all those.
Because the loan numbers are numerical and the reporting is "loan1, loan2, etc.", I can't use XLOOKUP with the other match options, since there is no "lookup_value in lookup_array" that I would use, for example in Python.

Current solution now is to just manually find the related loans and sum them up, but the problem is that if I want to sort the table with something, the cell references will not be valid. I would really like to avoid just getting the sum and adding that as value only to the cell.

Is there a solution that can:
a) Find an exact match in string
b) Lock cell references in relation to the loan number

 

[JGordon11]

Might be able to accomplish with a complicated formula.

Here's a UDF that should work:

Function SumLookup(LookupValue, LookupArray, SumArray, Optional Delimiter As String = ",") As Double
    Dim LA, lai, SA, LV, lvi, i As Long
    LA = LookupArray
    SA = SumArray
    If UBound(LA) <> UBound(SA) Then SumLookup = "Function returns value error if LookupArray dimension <> SumArray Dimension"
    LV = Split(LookupValue, Delimiter)
    For Each lvi In LV
        i = 1
        For Each lai In LA
            If --lvi = --lai Then
                SumLookup = SumLookup + SA(i, 1)
            End If
            i = i + 1
        Next
    Next
End Function

delme1.xlsx
	A	B	C	D	E
1	Loan	Related Loan	Amount	Related Amount
2	1	2	100	200
3	2	1	200	100
4	3		300	0
5	4	5,6	400	1100
6	5	4,6	500	1000
7	6	4,5	600	900
8
Sheet2

Cell Formulas
Range	Formula
D2:D7	D2	=SumLookup(B2,$A$2:$A$7,$C$2:$C$7)