Validating two sheets and typing text into a column for the results

[Andreas_Zopo]

Hi there!

I have a workbook with two sheets. One sheet represents current employees from a company and their full information for 2015-2016. The second sheet is the same information but including 2016-2017.

I need to run a formula on the sheet "Data" on Column H that checks the names on sheet "Data" against the names on sheet "Current" and if there's a match, type "Yes" in Column H.

The EIDs are not as beautiful as the example I'm attaching for they're all scrambled but I do have a consistent EID I can use for referencing. I tried several different formulas but I failed miserably and thought of looking for help here.

Thanks for all your help!
Andreas

[Debraj Roy]

In case .. you only want to check with names.. (First & Last name.. then.. in H2..

=IF(ISNUMBER(MATCH(B2&C2,Current!B2:B1000&Current!C2:C1000,0)),"YES","NO")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Andreas_Zopo]

Hi Debra!

Thanks for your response. This formula seems to work great if names match the lines on both sheets but that's not the case. I think it's best to search by EID. I tried this:

=IF(ISNUMBER(MATCH(A2,Current!A2:A1000,0)),"YES","NO")

But it didn't work

Any thoughts? :P

Thanks!

[Debraj Roy]

Its working fine for me..
Try to fix the range..
=IF(ISNUMBER(MATCH(A2,Current!$A$2:$A$1000,0)),"YES","NO")

[Andreas_Zopo]

Something weird is going on. It's working for the first 20 rows or so but the rest say "NO" but if I double click on the EID cell, then suddenly some cells switch to YES. LMAO What is this? is it like a format problem? I have to manually do it on every cell

[Debraj Roy]

check your calculation option..
Formulas > Calculation > Calculation OPtion

It may be set to Manual.. if then..change it to automatic..

[Andreas_Zopo]

The problem is not the formula (THe calculation is set to Automatic), it's the Column A where the EID is stored. Seems like there's a format or something. The numbers are aligned left, then when I double click and deselect the cell, the numbers align right and it becomes "readable" by the formula and that's when the "yes" appears if it's the case.

I'm double-clicking each cell manually. I'm at a loss of words.