IF statement recognising multipe entries in one column and applying result to group??

**pedro1982** · 01-07-2012, 03:15 PM

Help!

I have a range of values in one column with multiple entries for each value, and want to return for another column range if a certain value appears for any of the values in the first column....

So if for the values in the range of column A, say 1, if any values in the range in column B = 'correct' then this should apply to all values for 1

ID RESULT
646 CORRECT
646 INCORRECT
646 INCORRECT
1233 CORRECT
1233 INCORRECT
5555 INCORRECT
5555 INCORRECT

So if for 646 any result is 'correct' return a 1 for all entries of 646?

Really being stupid here but help!!!

**pedro1982** · 01-07-2012, 03:28 PM

sorry that should say if for value 646 in column A any entry in column B is 'correct' then return Yes for all entries of 646????

**JieJenn** · 01-07-2012, 03:30 PM

I am assuming your data starts in row 2.

Try this

=IF(AND($A2=646,$B2="CORRECT"),"YES","")

**zbor** · 01-07-2012, 04:39 PM

JieJenn, we are not supposed to answer questions that are against rules.

pedro1982, please rename thread.

Please read and adhere to these simple rules!

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

**pedro1982** · 01-07-2012, 04:47 PM

jiejenn,

thanks for your reply the only issue being i have +20k lines of data so cannot type in the value of 646 etc in the formula, also as id 646 appears multiple times the formula is to determine if any of the values in column B against 646 is a certain value???

**pedro1982** · 01-07-2012, 05:13 PM

JieJenn

so i have it to now say =IF(AND($U$2:$U$5519,U2,$BE$2:$BE$5519="CORRECT"),"YES","") which returns Yes when the ID in U is CORRECT in BE however i need this to apply to each line for the ID in column U? So if U = 646 and 646 appears 4 times in column U but BE is "CORRECT" for one of these and "INCORRECT" the other 3 the formula should recognise that "CORRECT" appears and apply Yes to each instance of 646?

**zbor** · 01-07-2012, 05:15 PM

pedro1982, again, please rename thread according to the rules!

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.