Lookup/ Partial Match

[saenzj]

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?

 

[Chrisdontm]

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)

 

[saenzj]

Thank you @Chrisdontm, this is exactly what I was looking for.