XLOOKUP with partial match or working relative cell reference

mummo

New Member
Joined
Apr 2, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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
    Capture.JPG
    37.1 KB · Views: 8

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
293
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,148,195
Messages
5,745,285
Members
423,942
Latest member
excelhelp1423

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top