Use formulas to find a match

[vanzie]

Hello everyone

I have an access database and there are 2 tables containing information. Table 1 contains all the offenders' details with columns ID and Full names and table 2 contains all the offenses that the offenders committed with a column full names and another column that is linked to the offender's ID field.

I recently designed an upgraded database of the existing one but now in table 2 the offender's ID column is empty as this was added to show the offenses that are linked to the offender

Now I exported both tables to excel and this is the part where I'm stuck at. Column A is the ID field of the offenses. Column B is the Full Names of the offender who has committed the offense. Column C is the offender's ID number linked to his name. In column D and E are the offender's ID and Full Names that is the lookup array so that it can fill in the ID field in col. C with the matching ID found in col. D.

I've tried vlookup but it seems I can't get it to work. I've also tried index match but no luck

[CK76]

Without sample workbook, bit hard to help you.

Desensitize sensitive info, while keeping data structure and type intact. To upload file, see instruction in the yellow banner at the top.

[vanzie]

find attached spreadsheet

There's no sensitive information. Just random names. The other information I removed from the sheet

[CK76]

There's extra space(s) in names in E column.

Add a helper column F.
=TRIM(E1)

Copy down.

Then use INDEX/MATCH or VLOOKUP using F as lookup column.

In any sort of data analysis, calculation. It's important that data transformation/clean-up is performed first. I'd even venture to say, that will be bulk of the work

[vanzie]

Thank you sir. That looks really good. I did notice some names captured in column B doesn't match in column F but I will sort that problem out because I want to get the stuff organised and remove incomplete data

[CK76]

You are welcome

If you are satisfied with the solution provided. Please mark the thread as solved, using thread tools found at top of your initial post.