saenzj

New Member
Joined
Mar 14, 2017
Messages
3
Hello, I am trying to create an aging table for receivables. I have a report that gives me all the detail of what is outstanding but I want to convert the data into 5 buckets. The report gives me the name of the individual, amount, and what term it was from.
Name
Balance
Term Description
xxxx
17,375
2018 Summer Term

<tbody>
</tbody>

I created a reference table to give me the month of the term.
Term
Month
Fall Term
9
Spring Term
1
Summer Term
6

<tbody>
</tbody>

My plan was to combine a if statement, isnumber, & search to search the term description and look for it in the reference table. Then I wanted it to return the Month number from the reference table. I was then going to add "&"/"&MID(Term Discription,1,4)". My goal was to return the value of 6/2018 but I can't get it to work. Any tips on a formula that I can use to accomplish this?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
810
Since there are no cell references where all this information is, I will make it up.
Lets say that the report is in A11:C12.
Then lets say that the place that you want the formula that will give the results is in D12.
Then lets say that the reference Table is in L3:M6.
So try putting this formula in D12;

=VLOOKUP(RIGHT(C12,LEN(C12)-5),$L$3:$M$6,2,0)&"/"&LEFT(C12,4)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,054
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top