Checking

[Azinferno]

How can I get excel to check B1-6 against E1-6 for duplicates. And check those 2 columns against data in sheet 2 that's used to fill those columns. And any names not used placed in H1-3

[#2]

Hi,

for the first part of your question use vlookup.

https://support.microsoft.com/en-us/office/video-vlookup-when-and-how-to-use-it-9a86157a-5542-4148-a...
https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

[#3]

@Azinferno 

Unfortunately you didn't mention on which version of Excel you are.

As variant:

To check duplicates you may apply conditional formatting rule to each of ranges

Names which are not in B1:B6 and E1:E6 - add in H1 formula

=IFERROR(INDEX(Sheet2!$A$1:$A$15,
    AGGREGATE(15,6,
        1/(COUNTIF($E$1:$E$6,Sheet2!$A$1:$A$15)=0)/
           (COUNTIF($B$1:$B$6,Sheet2!$A$1:$A$15)=0)*
        ROW(Sheet2!$A$1:$A$15),
ROW())),"")

and drag it down till empty cells appear.

If range starts not from first row of the sheet formula is to be modified, except range

ROW() - ROW(cell before start)

at the end.

In attached file they are applied to the first and last blocks.