Table lookup

[lauralen]

I hope I'm able to explain this well. I'm working on a loan report that shows current lending exposure by customer. I am not able to edit the report query at all, so all manipulation has to be done in excel. Because of the nature of the report (some loans are guaranteed by multiple people), each loan number is listed multiple times. I've figured out a way to isolate each loan number once and tie out the overall balance, but doing that required more manual work than I'd prefer, so that's what I'm looking to cut out.

The formula I use to do that is this: =IF(COUNTIF($D$13:D13,D13)=1,L14,"") inserted to the left of the table below so Account Number is column D and Principal Balance is column L.

The main problem with this is that the principal balance in column L only appears once per loan number and not always in the same place (e.g. 1st or 2nd or 3rd row of merged account cell). So if the formula I put in returned "-" I had to look into whether it actually had a $0.00 principal balance or was referring to a blank cell. Because of this, I can't just use fill up/down because it can be both or actually neither because some loans are actually all blank in column L. Example: Loan number takes up merged cells D21-D24 in its first instance. L21-L24 are all blank. But that loan number also appears in cells D54-D57 and there is a value of $100,000 in L56. My formula pulls "-" in D21 and " " in D54, so I manually go to the first instance, search for the loan number, find the principal balance, and copy it into L22.

Is there a way to insert a column next to Column L that has a principal balance filled in in every cell? VLOOKUP seems too limited to do this because account numbers are listed multiple times.

I'm also not glued to using this method. If there's a better way, please let me know. Though I should note here another method was tried by using a pivot table on another tab and was rejected by the powers that be because it's too hard to prove out or test that you are pulling every loan number exactly once.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suspect that you have had no responses up till now because to test, a helper would need to set up a sheet like yours. That is a lot of manual typing, merging cells etc.
If you can provide the sample data (& include the expected results) in a form that helpers can copy/paste then I suspect a suggestion would be forthcoming. My signature block below has help with how to do that.

 

[SpillerBD]

Merged cells will create certain problems and reminds us this is not DATA but is a report.
You will want to use try PowerQuery/Get and Transform to get the information into a proper data table.

If you can un-merge the Cells you do a fill down (F5, select Blanks...)
With the Rows properly filled, you use a SUMIFS to get the Principal balance.

 

[Peter_SSs]

Whilst I agree with SpillerBD that merged cells can be a problem, there are times when they are useful and I think we can get what you want without removing them. Here is a smaller sample data using an extra helper column (M) to get the result (I think) you asked for in column N.

I have assumed
- that there will be only 1 PB per Account Number in the table. If that is not the case then please give details of what is required in that circumstance.
- that the Account numbers in column D are numbers stored as text though XL2BB below makes it appear otherwise.

Book1
	B		D								L	M	N
1	Name		Acc Num								PB	Help	PB
2	ABC LLC		5111									5111	1
3											1	5111#	1
4												5111	1
5	DEF LLC		5112									5112	5
6												5112	5
7												5112	5
8			5113									5113	3
9											3	5113#	3
10												5113	3
11	GHI LLC		5114									5114	4
12												5114	4
13											4	5114#	4
14	JKL LLC		5112									5112	5
15											5	5112#	5
16												5112	5
Sheet1

Cell Formulas
Range	Formula
M2:M16	M2	=LOOKUP(REPT("Z",99),D$2:D2)&IF(L2="","","#")
N2:N16	N2	=INDEX(L$2:L$16,MATCH(SUBSTITUTE(M2,"#","")&"#",M$2:M$16,0))

 

[lauralen]

=INDEX(L$2:L$16,MATCH(SUBSTITUTE(M2,"#","")&"#",M$2:M$16,0))

This is pretty much exactly what I was looking to do! I had tried to use match and index earlier with no success, but I hadn't thought to add in a column essentially unmerging the account numbers. Thanks so much!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

On further reflection, since I already assumed that there is only one Principal Balance per Account Number, the formulas could be simplified further.

Book1
	B		D								L	M	N
1	Name		Acc Num								PB	Help	PB
2	ABC LLC		5111									5111	1
3											1	5111	1
4												5111	1
5	DEF LLC		5112									5112	5
6												5112	5
7												5112	5
8			5113									5113	3
9											3	5113	3
10												5113	3
11	GHI LLC		5114									5114	4
12												5114	4
13											4	5114	4
14	JKL LLC		5112									5112	5
15											5	5112	5
16												5112	5
Sheet2

Cell Formulas
Range	Formula
M2:M16	M2	=IF(D2="",M1,D2)
N2:N16	N2	=SUMIF(M$2:M$16,M2,L$2:L$16)