How can I match a partial data set with a full one in a spreadsheet?

Question

I have a spreadsheet (in Google Docs, but can be any spreadsheet) with a list of users (one per row), including a username (and other columns). Lets say there are 'n' rows.

I have another spreadsheet with less than 'n' rows. One column has a username, another column has some other piece of data (lets say car rego number). I want to move the car rego number to the corresponding user in the first spreadsheet.

How might I do this?

Answer 1

How about this (I'm thinking in Excel, if it matters)

Copy the second worksheet into the first workbook.

Use a VLOOKUP to find the user, and get the value

In the target column (car rego) of the first workbook try this formula: VLOOKUP(username, otherworksheet_username_range, car_rego_column_number, False)

This will produce "#N/A" errors when the username doesn't exist in the second worksheet. You could either then sort by car rego and delete these values out, or you could use on of the Excel ISErr, or ISNA functions to do an if test and display a blank if the VLOOKUP returned an error. (not sure of the exact functions, as I don't have Excel on this computer).

This would look something like this (adding an extra column for the VLOOKUP, where VLOOKUP=the formula from above).

COLUMN1...........COLUMN2

VLOOKUP...........If(Iserr(VLOOKUP),"",VLOOKUP)

Let me know if thats clear or if you need more detail