Powershell: Searching array from imported CSV data using a For-each loop

Question

I have been tasked to write a powershell script that will take the data from 2 CSVs and information from Active Directory and send the output to a separate CSV. The unfortunate part is that there is no unique single column for each of the CSVs and I will need to query AD using first name and last name. The only consistent data across all 3 is the first name and last name.

I think it comes down to two questions.

1. How do I merge the 2 CSVs using first and last names to a third CSV?
2. How do I query AD using both the first name and last name and add that information to the third CSV? (I don't want to use "get-aduser -filter * | where" because the Domain is huge and so are the CSVs. )

CSV Data is structured Like this

CSV1

CSV2

Wanted output

EDIT
I see what I was doing wrong with querying AD with the multiple filters.

I was trying to use $row.lastname and $row.firstname as my filter instead of setting a variable.

$CSV1 = import-csv .\CSV1.csv
ForEach ($Row in $CSV1){
$ln=$row.LastName
$fn=$row.FirstName
get-aduser -filter {sn -eq $ln -and GivenName -eq $fn} | select UserPrincipalName
}

Answer 1

See if this works for you:

# just sample data -- to be replaced by CSVs
$csvone = @"
EEID,LastName,FirstName
1,Smith,Joe
2,Moore,Steve
3,Blow,Joe
"@
$csvtwo = @"
LastName,FirstName,JobCode
Moore,Steve,101
Smith,Joe,100
Blow,Joe,102
"@
$CSV1 = $csvone | ConvertFrom-Csv   # substitute Import-CSV here
$CSV2 = $csvtwo | ConvertFrom-Csv   # substitute Import-CSV here

# begin the aggregation
$csv1props = $csv1[0].psobject.properties.name
$final = @{}
# build the initial hash
ForEach ($l in $CSV1){
    $key = $l.LastName,$l.FirstName -join "*"   # unlikely to find asterisk in names
    if ($final.ContainsKey($key)){
        Write-Host "Found duplicate in CSV1: $key"
        Continue
    }
    $emp1 = [ordered]@{}
    foreach ($p in $csv1props){
        $emp1[$p] = $l.$p
    }
    $final[$key] = $emp1
}
# add the JobCode to the hash based on matching "LastName*FirstName" keys
ForEach ($l in $CSV2){
    $key = $l.LastName,$l.FirstName -join "*"
    if (-NOT $final.ContainsKey($key)){
        Write-Host "Didn't find '$key' from CSV2 in CSV1"
        Continue
    }
    $final[$key]["JobCode"] = $l.JobCode
    $u = get-aduser -filter {sn -eq $($l.LastName) -and GivenName -eq $($l.FirstName)}
    if (-NOT $u){
        Write-Host "Failed to find user '$($l.LastName) $($l.FirstName)' in AD"
        $final[$key]["UPN"] = "UNKNOWN"    
    }
    else{
        $final[$key]["UPN"] = $u.UserPrincipalName
    }
}
$final.keys |
    ForEach-Object{
        [PSCustomObject]$final.$_
    }