Excel/Combining use of Search and VLookup functions
QUESTION: Hi Aidan,
Using Windows 7, Office/Excel 2010
C2 = 11-06-2014JS
C3 = 11-06-2014JA
C4 = 11-06-2014MQ
C5 = 11-06-2014DD
C6 = 11-06-2014JA
C7 = 11-06-2014JS
C8 = 11-06-2014JT
C9 = 11-06-2014JO
C10 = 11-06-2014DR
C11 = 11-06-2014JA
C12 = 11-06-2014JM
Want any cell in column M (M2, M5, M10 in this scenario) to auto populate with the word “School” if the letters JS, DD, or DR are a part of the data in the corresponding cell in column C (C2, C5, and C10 in this scenario). The date portion of column C cells changes each week so I want the column M formulas to only look for the 3 sets of letters I indicated regardless of what date may appear in front of them.
ANSWER: C7 also fits the rules you gave - and this formula should do what you want
enter the formula in M2 and fill it down as required.
---------- FOLLOW-UP ----------
QUESTION: Thanks for the last formula. It works great.
I struggled to find a way to explain what I’m asking this time. Hope this makes sense.
As in the previous question, Column C cells have the “11-06-2014JS; 11-06-2014MQ; 11-06-2014DD….” type data which is from another agency source that I can’t control in terms of getting the data cleaned up (remove date and trim trailing spaces) prior to receiving it. I now need to convert the 2 character alpha codes into recognizable words/hearing types (Arraignment, Disposition, etc.) without the date into the corresponding column D cell.
I have a formula, =IF(C2="","",VLOOKUP(C2,VLookupTables!$B$2:$C$17,2)), that works well once I first remove the dates and trim trailing spaces. I’d like to avoid editing the data each time a new batch is sent from the other agency.
I want to know if the last formula you did for me can be modified to combine use of both the search and VLookup functions to decide what word to put in the corresponding column D cell base on the 2 character alpha code in Column C.
if the data you want is always at the last two non space characters of the data you get returned, then using the TRIM and RIGHT functions would get you the data you want
would demonstrate this and updating your formula you will get
IF the number of possible values is relatively small, my original method could also be modified, but the method above would be cleaner.
Happy to help further if required, and if it helps my direct email is firstname.lastname@example.org