You are here:

Excel/Combining use of Search and VLookup functions

Advertisement


Question
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

=IF(NOT(AND(ISERROR(SEARCH("JS",C2)),ISERROR(SEARCH("DD",C2)),ISERROR(SEARCH("DR",C2)))),"School","")

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.

Answer
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

=RIGHT(TRIM(c2),2)

would demonstrate this and updating your formula you will get

=IF(C2="","",VLOOKUP(RIGHT(TRIM(c2),2),VLookupTables!$B$2:$C$17,2))


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 aidan.heritage@virgin.net
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.