Cross Verify Two Excel Worksheet

[dip694]

Hi All. I am looking for some guidance or some help on a project I am working on. I work in the aviation field and looking to hire some pilots. From the FAA's website you are able to download all of the airmen's information (link below). When downloading the file, you will see Pilot Basic and Pilots Cert. Pilot Basic provides name, address, and medical information. Pilot Cert provides name and aircraft this pilot is type rated on. What I am trying to accomplish is the following:

1. Filter all the pilots on Pilot Cert to only show CL-30 rated pilot (LIST 1). I know how to do that through the filter commend. Filter all the pilots in Pilot Basic to only show the pilots based in a particular state (LIST 2). Again, I know how to do that through the filter commend.
2. Now this is where I need your help is that I am trying to easily identify all the guys from LIST 1 that are in LIST 2. What is the best way to achieve that? Note that both field has a UNIQUE ID associated to that pilot that matches on both worksheet.

Any help would be greatly appreciated. Thank you.

LINK: FAA Airmen Database

 

[Randall]

Assuming that LIST 1 and LIST 2 are PILOT_CERT and NONPILOT_CERT. If that's the case, in cell R2 of NONPILOT_CERT, type in =XLOOKUP(A2,PILOT_CERT.csv!$A:$A,PILOT_CERT.csv!$A:$A) then you can filter out whatever you need (matches vs non-matches).