Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-05-2014, 04:06 PM
Tralee6 Tralee6 is offline VLook up search Windows 7 32bit VLook up search Office 2007
Novice
VLook up search
 
Join Date: Aug 2014
Posts: 3
Tralee6 is on a distinguished road
Default VLook up search

Hi, On one tab the cell contains a text string that includes the unique id number is within quotes (i.e. Employee number "12345" failed skills test). On another tab there is a listing of all employee numbers in one column with first and last names in separate columns. Can a Vlookup search the text for the first set of quotes, and match it to information on the second tab?

Tab 1


Column A, cell 2
Employee number "12345" belongs to department 19.

Tab 2
Column A, cell 2
12345
Column B, cell 2
Lname
Column C, cell 2
Fname
Column D, cell 2
Location
Reply With Quote
  #2  
Old 08-05-2014, 08:16 PM
excelledsoftware excelledsoftware is offline VLook up search Windows 7 64bit VLook up search Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

The Vlookup can be combined with other formulas but not for ranges so if I understand right this can be done in 1 of 2 ways.
VBA or making another column of data to make the vlookup work.

Since we are on the Excel forum I will show you how you can do this by making another column.

On Tab 1 you will need to insert a column to the left of A. This will push your original data to column B. Now write the formula in cell A2
Code:
=MID(B2,18,5)+0
and drag that all the way down. Now this is assuming that all of your employee numbers are 5 characters. It that is not the case it is certainly possible to write a combination of mid and search formulas to extract out the number but I personally would just do it in VBA.

After you have this column you can then use your vlookup to pull the data that you need. Remember the + 0 so can you convert the mid text value into a number or the vlookup will not work.


If anyone has a different approach to do this with formulas I would love to see it since I've already done quite a few experiments to grab it and they havent worked.

Thanks
Reply With Quote
  #3  
Old 08-06-2014, 05:39 AM
Tralee6 Tralee6 is offline VLook up search Windows 7 32bit VLook up search Office 2007
Novice
VLook up search
 
Join Date: Aug 2014
Posts: 3
Tralee6 is on a distinguished road
Default VLookup

Thank you for the quick response. Attached is a sample file.


What I'm looking for is to see if it is possible to set up a formula that will look at Errors tab, column B, see the numeric within the quotes, then go to tab Census and pull to tab B the EE location, EE division, etc.
Attached Files
File Type: xlsx TEST FORUM.xlsx (11.6 KB, 11 views)
Reply With Quote
  #4  
Old 08-06-2014, 11:10 PM
excelledsoftware excelledsoftware is offline VLook up search Windows 7 64bit VLook up search Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Looking at this data I see 2 ways to pull it off. As far as a formula goes to extract out the number in quotes I think it would be much easier to write that part in VBA. The other thing you can do without the use of VBA is to use the Text to Columns. Use the " as the delimiter and it will separate those number automatically. Then you can run all the formulas you want to grab the data which would be pretty easy at that point.
Reply With Quote
  #5  
Old 08-07-2014, 05:20 AM
Tralee6 Tralee6 is offline VLook up search Windows 7 32bit VLook up search Office 2007
Novice
VLook up search
 
Join Date: Aug 2014
Posts: 3
Tralee6 is on a distinguished road
Default

Thank you!! Very much appreciate your help.
Reply With Quote
Reply

Tags
vlookup

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search for date and then apply mutliple search criteria in huge dataset maxtymo Excel 2 12-01-2013 04:52 AM
Looking for Windows Search app with ability to search by content gopher_everett Office 1 02-28-2013 09:23 PM
VLook up search AND/OR/NOT-search joblo108 Excel 3 09-09-2011 04:23 AM
Instant Search's "Display search results as I type when possible" with Exchange lwc Outlook 0 06-01-2011 01:56 AM
Search and Replace - Clear Search box JostClan Word 1 05-04-2010 08:46 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft