Cross check an old list against a new list

[DaveMacdonald]

Hello,

I have a roster of employees where A is last name and C is first name. I have a new list of folks that require a certain training that I want to record in AE. Is there a way to check each name in Columns A and C against a new list (columns A and B) and if the first list name is on the second list, to place a "x" in the original lists AE Column? and do to that for the whole table, (2:264)?

Sheet 1

A	C	AE
Smith	Dorothy	x (Formula Here). I've tried =IF(AND(A:A='sheet2'!A:A,C:C='sheet2'!B:B),"x","") but that didnt work. I'm not entirely sure how vlookup/xlookup works but I thought that was my next step. I dont know though. It's got to be a match for both first and last names, theres a lot of folks with the same first or last name on the list.
Amos	Jack
Smith	Harry

Sheet 2

A	B
Smith	Dorothy
Amos	Sandy
Peach	James

 

[alansidman]

you can join your two tables in Power Query. Load each to Power Query and then do a full join. Here is the Mcode for the full join

let
    Source = Table.NestedJoin(tblOld, {"Last", "First"}, tblNew, {"Last", "First"}, "tblNew", JoinKind.FullOuter),
    #"Expanded tblNew" = Table.ExpandTableColumn(Source, "tblNew", {"Last", "First"}, {"tblNew.Last", "tblNew.First"}),
    #"Added Custom" = Table.AddColumn(#"Expanded tblNew", "New", each if[Last]=[tblNew.Last] and [First]=[tblNew.First] then "x" else null)
in
    #"Added Custom"

and the result of the join

Last	Column1	First	tblNew.Last	tblNew.First	New
Smith		Dorothy	Smith	Dorothy	x
			Amos	Sandy
			Peach	James
Amos		Jack
Smith		Harry