Formula for finding data in one spreadsheet to paste in another, but not hlookup??

[Tihocan]

Please help I am tearing my hair out with this. Technically I could do it manually and it probably wouldn't take any longer than a couple of hours, but I really want to learn formulas. What it is is I've been asked to input payroll numbers onto a spreadsheet that has been created from a query on a staff training database, unfortunatley the database didn't have the payroll numbers on it.

So in my spreadsheet (that was run from the database) I have created a blank column in a, this is where I want the payroll numbers to go. In column b is a first name, column c is the surname, column d onwards is all other kind of info that was brought over from the database. There are thousands of rows because each member of staff had more than one record. I need to transfer the payroll numbers across from another spreadsheet that I have. In this other spreadsheet the payroll number is in a, first name in b, and surname in c.

I have tried Hlookup and Vlookup but I don't think this works for my type of query. Is there anything in excel that can do this?

Thanks!

[Craig K.]

If you have a dump of info from a database, I would suggest using a pivot table. It is extremely customizable in how you would want to view your info.

[Pauleyb]

Trying to do a 'double lookup' (i.e. a lookup for the first name and then one for the surname) is complex. Highly recommend you create some 'helper' columns in each sheet. Each sheet should have a column added which concatenates the first and surnames. Something like this =B1&C1.

Since VLOOKUP uses the first column as its 'search source' you need to copy the payroll numbers next to it. So, in your sheet with the payroll numbers: A is payroll number, B is first name, C is surname, D is your combined names (=B1&C1), and E is a copy of column A (=A1).

Then, in your big database sheet, your new column A would be the two names (=C1&D1), your new column B would be =VLOOKUP(A1,Table_with_columns_D_and_E_on_other_sheet,2,FALSE), and then C would be the first name and D the surname, followed by the rest of the data.

[Tihocan]

I've done it! Pauley thanks so much for your advice I tried it step-by-step this morning and it worked I couldn't believe it! It's amazing I love excel my inner geek is coming out now. I also managed to do a copy>paste special>values on my payroll numbers so they are not linked to the formula so I could delete all my helper columns. Totally brilliant I am going to keep a note of everything I learn so I can refer back to it. Thank you so much.

Craig, I will be onto Pivot Tables next week!!!

Have a good weekend folks!