check for match of cell to range of cells only if another cell is equal to X

[rfigueroa1976]

I知 trying to figure out a formula and am hoping you might be able to help. Attached is a sample worksheet and the explanation of what I知 trying to figure out.

What I知 trying to do is flag a field if conditions are met ( First check the fiscal year. If the year is X, check list 1, if the year is Y, check list 2: )

More specific to the worksheet: If cell F2 of sheet 1 is < 2016, look at range a2:a6 of sheet 2 and if there is a match to cell a2 of sheet 1 , result should be "YES", else if no match "NO"; otherwise, If cell F2 of sheet 1 is equal to 2016, look at cells a7:a25 of sheet 2 and if there is a match to cell a2 of sheet 1, result should be "YES", else if no match "NO" ---- The only way I can think of the formula is as follows: =IF(f2<>2016,(COUNTIF('sheet2'!A$2:E$6, A2),"Yes",IF(f2=2016(COUNTIF('sheet2'!A$7:A$25, A2),"Yes","No")). Obviously, this is not working.

I致e looked online but have had no luck. I知 hoping you can help me out.

I tried to attach the worksheet but this is my first post so i'm not sure how to.

[skywriter]

Click the go advanced button, then choose the paper clip icon from the new tool set that comes up and then manage attachments.

[rfigueroa1976]

Yeah. I tried that but a blank screen came up... i thought it might be the browser (Chrome) so i tried IE and still get the blank screen... No buttons, text or anything to let me upload a worksheet.

[rfigueroa1976]

Had to upload from my phone. Must be my company's firewall.

[skywriter]

So in the description of what want to do on your attached spreadsheet, you state look at cells A2:A6 on sheet 2. Sheet 2 has sku numbers in the A column that go from row 2 to row 25, so did you mean A2:A25? Then later you state if criteria aren't met you want to now look in A7:A25 for something else. At the bottom of your request you state how you thought the formula should look and you reference A2:E6 in your formula. Your data goes from A2:C25, then at the end of your formula you state A7:A25. I think you should clear up your request to match your actual spreadsheet, I don't want to guess at what you want and go back and forth.

[rfigueroa1976]

Sorry about the confusion. The range of skus is in column A of sheet 2 so the E6 reference was my bad.

To answer your first question, yes, the sku listing is on sheet 2; however, the list can technically be devided into two. 2015 skus (a2:a6) and 2016 skus (A7:A25).

What I'm trying to do is this:

If the Year (Sheet 1 - cell f2) is NOT equal to "2016", check to see if the sku (sheet 1 cell a2) has a match to any of the 2015 sku list (sheet 2 cells a2:a6). If there is a match "Yes"
If the Year (Sheet 1 - cell f2) is equal to "2016", check to see if the sku (sheet 1 cell a2) has a match to any of the 2016 sku list (sheet 2 cells a7:a25). If there is a match "Yes"
Else, "No"

=IF(f2<>2016,(COUNTIF('sheet2'!A$2:A$6, A2),"Yes",IF(f2=2016(COUNTIF('sheet2'!A$7:A$25, A2),"Yes","No"))

I hope this clears things up and sorry if I'm giving you a headache LOL!

[skywriter]

I hope this clears things up and sorry if I'm giving you a headache LOL!

No worries, you are not giving me a headache. What happens is if one makes assumptions as to what the person who posted wants, when it is not clear, it may become a headache as they go back and forth trying to get it right. I think you cleared it up nicely and I hope you like the solution.

Good Luck!!!