Savings & Debt - Account Balance will not auto populate

Google Sheets
,
1

My issue is related to this previous post.

The original issue has been solved but now I am faced with a new issue. Although the I am able to see my accounts in the drop down, when the account is selected, the balance will not auto populate and remains blank. Balance is Column O ={“BALANCE”;arrayformula(if(isblank(L7:L),iferror(1/0),iferror(vlookup(L7:L,{AM7:AM,AR7:AR},2,false))))} under Debt Payoff Goals. The account in question is a manual account, I wonder if this somehow has an effect?

Let me know if additional information is required. Thank you in advance.

2

Did you check if the account you were selecting in column O is listed in column AM and that it has a balance in AQ?

3

Hey Randy. Yes it is listed in column AM and is showing a balance in AQ.
it is also listed in AI.

4

Sorry… I meant that the balance is coming from AR (not AQ). Is it there?

5

Yes it is in AR and in AQ

6

would any of the formulas help or maybe i could send some screenshots? let me know

7

That is pretty confusing. The formula is straightforward. It should look for a match for the value in column L in column AM and then return the value of the corresponding row in AR.

What if you just drop this minimized version somewhere in the spreadsheet?
=vlookup(L7,{AM7:AM,AR7:AR},2,false)
Where you replace L7 in the formula with a reference to the column L row that is failing to return a balance.

Does that work?

8

Can you please clarify what you mean by " a reference to the column L row that is failing to return a balance." ?

I agree, its confusing! it should work because the data is there. And i definitely couldn’t figure it out on my own.

9

without changing anything in the formula , the error i get is " Did not find value ‘Ford Explorer Financing’ in VLOOKUP evaluation."

10

Like if the account missing the balance is in cell L9, then run the formula in an empty cell in the sheet as:
=vlookup(L9,{AM7:AM,AR7:AR},2,false)

11

I understand now, thank you. The error message is : Error Did not find value ‘Ford Explorer Financing’ in VLOOKUP evaluation.

12

But you said you see that account in column AM? Can you confirm there aren’t extra spaces or type case issues?

13

Yes, I can see it in AM. No typecase issues, everything aligns.

14

I would really focus on finding out why that single-cell VLOOKUP doesn’t work… because it should.
The formula is simple so there must be something subtle going on with the lookup that we can’t see in this thread.

15

You’ve been a huge help Randy. Thank you.

16

@dmitriy.sidelnik - did you figure it out? If so, can you please post the solution here in case others run into it? Thanks!

17

use len() formula. make sure txt matches exactly.

18

Are you saying that you used the LEN() formula and found that some of the account names that should have matched had whitespace?

19

embarrassed to admit - but yes indeed that’s exactly what the issue was. :sweat_smile: You were right (again)

20

The TRIM() function is pretty cool for helping with this issue… or just being dilligent about not getting empty-space cruft in your data. :wink:

1 Like