Searching for data across different worksheets

[rgallimore]

Hi,
I have browsed through existing threads and other sites and didn't find this exact scenario. I'm sorry if this type of question is common. I appreciate any help.

I have worksheet1 named: 'My People' that has the following columns
ID, First Name, Last Name, Age, Employement, Employer, Devision, employment status,M/F, Mobile (I have created this last column titled 'mobile')

Worksheet2 named: 'Phone List' contains the following columns
Service Description (peoples named), Level 1, Level 2, Level 3, Level 4, Level 5, Level 6, CostCentre, Service No (peoples mobile numbers)

the two worksheets have different lists of ppl and sheet 1 name is seperated across two columns (first then last), sheet 2 fist and last name is combined (first last).

What I am trying to do is to search for ppl in worksheet1 that appear in worksheet2 and if they have a mobile number have it appear in worksheet1 under the new column I created titled 'Mobile Number'

I would appreciate any assistance

Thanks,
R

[JBeaucaire]

Hi RG, welcome to the forum.

Go ahead and just upload your sheet with a good usable set of sample data. Put in your desired "result" and point it out. If you show a few of them it will be very evident what you want. That helps us help you.

Click on GO ADVANCED and use the paperclip icon to upload.

[rgallimore]

Here is a document.

I can't upload the original because of the information it contains.
So I have created a similar document.

Thank you for you help

R

[JBeaucaire]

Adding that FULL NAME column pretty much solved the problem. Now a simple INDEX/MATCH formula will work.

It's up to you to insure the data in the FULL NAME column matches the names in the second sheet, your sample sheet did not (lots of extra invisible spaces) that I had to clean up.

Anyway, the first formula in N8 would be:
=INDEX('Phone Numbers'!C:C,MATCH(F8,'Phone Numbers'!B:B,0))

Then just copy that cell down. The names that don't match will give an error, the ones that do will show the Service Number from the second sheet.

I added a trim() function into your FULLNAME column to help.