Comparing 2 spreadsheets - to get data on 1

[Sarah Christian]

Hello,

I am hoping someone can help, I need to compare to 2 spread sheets and then produce a date found on one spread sheet if the 2 items match.
For example - Spread sheet one is a data dump and spread sheet 2 is an invoice.
If the employee number on spread sheet 2 matches spread sheet 1 then I need the dates and hours from spread sheet 1 to be shown in multiple cells on spread sheet 2.
I have tried VLOOK ups, but must admit I don't fully understand them (yet), so any assistance or tips would be greatly appreciated.

I hope this makes sense?
Thanks

[millz]

In order for VLOOKUP to work, your column that holds the employee number in spread sheet 1 must be on the left of the column that holds the dates and hours.

You didn't post a sample workbook so it's a little hard to visualize. Here is something you can try.

Let's say your employee number column is column A on both worksheets, and in spread sheet 1, the dates and hours are in column B

In cell B2 of spread sheet 2, enter this formula

Please Login or Register  to view this content.

Change NameOfSpreadSheet1 to the exact name of your spread sheet 1, if it contains spaces, cover the name with single quotes, like this: 'Name of Sheet 1'

if your dates/hours column are further away, let's say column G, you would change A:B to A:G, and then 2 to 7. This number represents the Nth column of the range (in this case A:G) you want the value returned from.

[Sarah Christian]

Thanks Heaps, but I think I should have given more details. So i have attached an example spread sheet. On the invoice I need to show all days (on separate lines) from the data dump sheet, for the employee that matched cell C22 on the invoice tab.

[Pepe Le Mokko]

In order for VLOOKUP to work, your column that holds the employee number in spread sheet 1 must be on the left of the column that holds the dates and hours.

It is actually possible to use VLOOKUP when the lookup column is on the right side of the data to be picked

Please Login or Register  to view this content.

where col D is the LOOKUP column and col B contains values to be looked up

[millz]

Thanks Heaps, but I think I should have given more details. So i have attached an example spread sheet. On the invoice I need to show all days (on separate lines) from the data dump sheet, for the employee that matched cell C22 on the invoice tab.

Well, yes you should have done so, because it now seems a little too complicated to be done with just formulas.

You want to show all days on separate lines, that would also mean there will be duplicated dates (as per your data dump). Or do you take only rows with "Code" = "TRAV"? So for those lines of dates, their respective columns "Code", "Date" (duplicate?), "Hours", "Rate", "Amount" needs to be copied as well? Is my understanding correct?



@Pepe Le Mokko
Well, I didn't say it's not possible. In fact, in Excel's function dialog it states that Vlookup "looks for a value in the leftmost column of a table". What you shown is actually a very nice trick or workaround in cases where the lookup value is actually on the right, though what it actually does is still sort of "rearranging" columns so Excel looks on the correct leftmost column. Though I appreciate that I learnt something new from your post, it may not exactly be beneficial to OP. IMO, it's more trouble than its worth.

[Pepe Le Mokko]

In B28 enter following array formula

Please Login or Register  to view this content.

and pull down until no more results appear.
Do not forget to commit with Ctrl+Shift+Enter

[Sarah Christian]

Pepe Le Mokko, I can't seem to get a result with the formula above. I have entered it exact into B28 on the invoice sheet and I am getting a date of 00/01/1900.

[Sarah Christian]

Hi Millz,

Yes I do want to show duplicate dates. In column B the dates will be dupliacted, but in column C the codes will be different. I will then need to show the hours, rates and amounts as well on the invoice sheet.

Thank you so much for your help so far.