Vlookup?

Question

I'm working with two worksheets. They both have a unique identifier that I'm trying to combine. Basically, the first worksheet has a userID# and location, with approximately 600 rows of data. The second worksheet has the same userID#'s, with additional columns of personal information, i.e., phone#, ss#, etc. However, the second worksheet has 6100 rows of data, or users. I'd like to generate a report that matches the user#'s from the first worksheet with appropriate data, and eliminate all the other info., or at least show which user#'s are not recognized and which are? Is this a vlookup cell formula, match, pivot? Please advise.

Answer

gabe,

If you want to bring over selected data from the second worksheet onto the same row with the matching ID in the first worksheet, it would be a vlookup function problem if the UserID# were in the leftmost side of the data in teh second worksheet.

If you want to mark items that match or don't match, you can use the countif or the match worksheet functions.

=vlookup(A2,Sheet2!$A:$Z,2,False)

would bring over the value in the second column of sheet2 (the second worksheet) for the same matching row. If there is no matching row, the formula returns #N/A.

match is the same

=Match(A2,Sheet2!$A:$A,0)

it returns the row of the match or #N/A if not found

=countif(Sheet2!$A:$A,A2) returns the number of rows that match value in A2 in column A of sheet2. anything above a zero indicates a match was found (>1 could indicate duplicates/data problems). A zero represents that there is no matching value.

After entering any of these formulas in row 2 of Sheet1 (the first worksheet), then you can drag fill it down the column. all the formulas assume UserID# is in column A of both sheets.

Does that answer the question? If not or it isn't clear, post a followup and I will see if we can't get your question answered. Explain what you think is missing or what you don't understand.

-- Regard Tom Ogilvy