Lookup or match over several columns

[rosscortb]

Hello

Hoping you can help.

I have a master list of employees with Uniq ID based on their Staff ID, Name, DOB and NI number.

Each month I do a headcount report and a lookup from the masterlist to headcount sheet to apply their UnqID number.

If there staff number has not changed then thats fine but there is always a batch that return #N/A so I have to work out if they are new starts or if they are employees that have left and joined again with new Staff ID.

So I think I need to work out if their name appears in the master list and then if it does, then work out if their DOB or NI matches(don't always have that) and then I will know that they already have a UNQ ID. I have attached a sample if anyone can help me that would be appreciated.

Thanks

Ross

[Pete_UK]

You could use a formula like this in F2 of the Headcount sheet:

=COUNTIFS('Master list '!B:B,B2,'Master list '!C:C,C2,'Master list '!D:D,D2)

Copy down, and if it returns zero then you need to check the Master sheet and maybe add details.

Hope this helps.

Pete

[Mrrrr]

And to navigate more easily in the Master sheet, assuming there will be many names in there, you can add in G2 and drag down the following formula which will make a hyperlink to the cell in the Master list that corresponds to the name in Headcount sheet in column B.

Please Login or Register  to view this content.

I made it up to 499 names (B2:B500) but you can increase it if you have more names in the Master list.
In G2 it displays the value $B$2 with hyperlink to B2 in Master list, and so on.
The -33 in the end of the formula removes the name of the workbook which would also appear - instead of just $B$2, it would appear '[lookup demo.xlsx]Master list '!$B$2

If it's #N/A like in the case of John, it means that the name doesn't exist in the Master list at all.

I hope this helps.