#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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.
|
#5
|
|||
|
|||
Thank you!! Very much appreciate your help.
|
Tags |
vlookup |
|
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 |
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 |