You are here:

Excel/Search for part of cell value

Advertisement


Question
Dear Aidan,

I need your valuable help in below if this is possible:

I'm having an employees details sheet, column ("A"# showing employee names, #"B"# showing the joining date, #"C"# showing basic salary....etc.

I did some analysis on this file based on employee name. means, I created a separate sheet and extract summary info by using #Vlookup# formula to get the required data.

I got some external files from other departments to do a double check of my results from original file, The issue was, these files which came from other departments was having some of employee names by name only #without family name or mid name#, to test my working, I manually changed all employee names in the coming files and start acting VLOOKUP.

Is there a way to make this VLOOKUP searching for part of full name in column #"A"# ???

Will appreciate your help on this matter since it will help me avoiding waste time.


Example:
in my original files:
Master sheet having below info.

Column A        Column B      Column C     Column D
Johan Patrick   May-09        20,000
William Moran   Jan-07         9,900
Anderea Killy   Feb-12        30,000
Tom Queen       Sep-10        16,700
Nicolas Ven

Report sheet having the below

Column A        Column B          Column C
Johan Patrick   =vlookup#A2,'Master'!1:1048576,3,False#
William Moran   =vlookup#A3,'Master'!1:1048576,3,False#
Anderea Killy   =vlookup#A4,'Master'!1:1048576,3,False#
Tom Queen       =vlookup#A5,'Master'!1:1048576,3,False#
Nicolas Ven     =vlookup#A6,'Master'!1:1048576,3,False#

Departments submission having following data:

Column A          Column B      Column C     Column D
Tom Queen          XXXXXX        ZZZZZ        YYYYYY
Nicloas V  
William M   
Anderea Russel Killy
  
     
as mentioned above, names came not exactly match with names in my file.


Regards
Mahmoud

Answer
It's always difficult when data doesn't exactly match - but there isn't (as far as I know) a quick way of handling this - however, you can do it in a number of passes - firstly, you've done a look up which resolves the exact matches, so they are fine.  You will then need to have a blank column to the left of the current data where you can enter formulas - the first to do would be to pick up the name and first letter of the family name only - put the formula

=LEFT(B2,SEARCH(" ",B2)+1)

where B2 equates to the name (having moved it across one column) - this formula will give everything up to the first space AND the space, and the next character - you can then do a lookup against this.  My GUESS is that this would resolve most of the issues, so it may be easier then to find the records manually that are still missing data and either amend the lookup to match, or just copy and paste the missing data.

I hope that this helps but do let me know if I can help further - I debated suggesting ensuring the submission data was sorted in alphabetical order and then doing a vlookup with a TRUE setting rather than false, but I felt that the possibility of error was too great to risk when dealing with personal data.
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.