Assistance with IF function

[DoggoS]

Hi there,

I have a workbook containing 2 sheets with various employee data - one sheet named 'Asset Tracking' contains the Employee Email Address (in column D) and the other sheet named 'A34 Enrolled Users' contains Employee Email Address (in column D).

I need a formula to look up the Employee Email Address from 'A34 Enrolled Users'’ sheet and match it to the Employee Email Address from 'Asset Tracking’ sheet and if it finds it to then put ‘Y’ into the ‘New Device Enrolled’ (Column I) within the 'Asset Tracking’ sheet. Please note the 'A34 Enrolled Users' is updated on a weekly basis so hoping this doesn't impact the formula.
I hope that makes sense... but Ive added a snap shot of data I'm working with.
Grateful for any assistance - thank you in advance

 

[etaf]

use a countif()

so in your column I

=countif( 'A34 Enrolled Users'!D:D,D4)
then use an IF to get YES & NO
=IF( countif( 'A34 Enrolled Users'!D:D,D4)>0, "yes","no")
copy down

I have D:D , that should stay when you update the sheet and use ALL rows - I dont like using column references , as refers to >1million rows , but in this case maybe OK

 

[DoggoS]

use a countif()

so in your column I

=countif( 'A34 Enrolled Users'!D:D,D4)
then use an IF to get YES & NO
=IF( countif( 'A34 Enrolled Users'!D:D,D4)>0, "yes","no")
copy down

I have D:D , that should stay when you update the sheet and use ALL rows - I dont like using column references , as refers to >1million rows , but in this case maybe OK

Hey thank you for the above, appreciated as always

=IF( countif( 'A34 Enrolled Users'!D:D,D4)>0, "yes","no") - this formula seems to be returning 'false'?

Please note this is just a snap shot of data I'm working with - the real data I am working on currently contains 400 rows, sorry I'm not sure if this makes a difference.

 

[etaf]

no the rows should not
should not return false
check the commas
as FALSE i have set as NO

=IF(COUNTIF( 'A34 Enrolled Users'!D:D,D4)>0, "yes","no")

copy the formula you have used here

Book4
	A	B	C	D	E	F	G	H	I
1
2
3
4				fred-1@email.com					yes
5									no
6				fred-1@email.com					yes
7									no
8									no
9									no
10				fred-2@email.com					yes
11									no
12									no
13									no
14				fred-3@email.com					yes
15									no
16
Sheet2

Cell Formulas
Range	Formula
I4:I15	I4	=IF(COUNTIF( 'A34 Enrolled Users'!D:D,D4)>0, "yes","no")

Book4
	A	B	C	D	E	F
1
2
3
4				fred-1@email.com
5				fred-2@email.com
6				fred-3@email.com
7
8
9
A34 Enrolled Users

 

[Joe4]

Didn't you already ask this question and have it answered here?

Assistance with Vlookup

Hi there, I have 2 sheets containing various employee data - one sheet named 'Samsung Replacement Master' contains the Employee Email Address (in column D) and the other sheet named 'A34 Registered Users' contains Employee Email Address (in column H). I need a formula to look up the Employee...

www.mrexcel.com

 

[DoggoS]

no the rows should not
should not return false
check the commas
as FALSE i have set as NO

copy the formula you have used here

Ignore me it's working on my test sheet but unfortunately returning incorrect data when using on my actual data sheet...Please note this sheet is saved on sharepoint and not locally on my PC.

 

[etaf]

not sure how sharepoint works these days , last used in 2000 ish
I would expect it to still use formulas ok,
BUT , can you answer
Joe4
not going to answer further if its already answered elseware , bit of a waste of time then

 

[DoggoS]

Didn't you already ask this question and have it answered here?

Assistance with Vlookup

Hi there, I have 2 sheets containing various employee data - one sheet named 'Samsung Replacement Master' contains the Employee Email Address (in column D) and the other sheet named 'A34 Registered Users' contains Employee Email Address (in column H). I need a formula to look up the Employee...

www.mrexcel.com

Hi Joe, working with slightly diff data this time around. The formula suggested on this thread initially worked but came across same issue that it was returning incorrect data on the actual worksheet I was working with.

 

[DoggoS]

not sure how sharepoint works these days , last used in 2000 ish
I would expect it to still use formulas ok,
BUT , can you answer
Joe4
not going to answer further if its already answered elseware , bit of a waste of time then

Totally understand, as per my reply to Joe it was different data working with 2 sheets....here I've combined all data in one workbook hoping it'll be more accurate.

 

[Joe4]

Hi Joe, working with slightly diff data this time around. The formula suggested on this thread initially worked but came across same issue that it was returning incorrect data on the actual worksheet I was working with.

Then you should continue in the original post instead of starting a new one.
Since it appears to be the same question (but with slightly different data - though you did not post any data in your original thread), please continue on in that other thread (please do not start a new thread on the same topic). Per forum rules, threads of a duplicate nature will usually be locked or deleted (rule 12 here: Message Board Rules).