# Lookup/ Partial Match

#### saenzj

##### New Member
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?

### 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
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

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

Replies
1
Views
44
Replies
0
Views
38
Replies
5
Views
83
Replies
6
Views
388
Replies
3
Views
371