# XLOOKUP with partial match or working relative cell reference

#### mummo

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

#### Attachments

• Capture.JPG
37.1 KB · Views: 14

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### JGordon11

##### Well-known Member
Might be able to accomplish with a complicated formula.

Here's a UDF that should work:

VBA Code:
``````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
ABCDE
1LoanRelated LoanAmountRelated Amount
212100200
321200100
433000
545,64001100
654,65001000
764,5600900
8
Sheet2
Cell Formulas
RangeFormula
D2:D7D2=SumLookup(B2,\$A\$2:\$A\$7,\$C\$2:\$C\$7)

Replies
4
Views
211
Replies
4
Views
122
Replies
0
Views
105
Replies
0
Views
220
Replies
5
Views
215

1,195,723
Messages
6,011,315
Members
441,604
Latest member
CraigThompson

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