Use formulae to check that 2 variables on sheet A appear in the same row on sheet B

[Carty]

Hi

I am trying to use formulae to check that the order number (sheet A, cell B4) and week due (sheet A, cell Q4 as YYWW) values are found in the same row of sheet B. The order numbers are stored in column E of sheet B, the due date is stored in column O of sheet B as WW.

Note the order number will only have 1 due date on sheet A but multiple due dates on sheet B


I am using match to find the given values on Sheet B:
To check the order number :

1 =MATCH(B4,SheetB"E:E",0)

Resolves to 12
To check the due date:

2 =MATCH(RIGHT(Q4,2)+0,SheetB”O:O”,0)

Resolves to 21


When I use

=IF(1=2,”Scheduled”,”Needs scheduling”)

returns Needs scheduling – correctly (12 doesn’t equal 21)
When I use

=IF(AND(1,2), ”Scheduled”,”Needs scheduling”)

returns Scheduled as the order and the due date appear somewhere in the columns but not necessarily in the same row.

I think I'm on the right path but can anyone offer guidance as to my next step(s) to get the formula to keep looking until the the rows to match?

Thank you

Paul

 

[jasonb75]

Perhaps

=IF(COUNTIFS(SheetB!E:E,B4,SheetB!O:O,Q4),"Scheduled","Needs scheduling")

Your psuedo formulas with invalid syntax are misleading so this is just a guess based on what little sense I could make of your question.

 

[Carty]

Jason

Thanks for your help.

Are you suggesting that I should have written the formula in it's entirety rather than shortcut it? Not offended by the comment just want to improve the way I pose questions to the forum

Paul

 

[jasonb75]

Hi, Paul.

I would say that an entire workign formula which you need changing, or a formula free description of what you need.

Some people will ask what you have already tried, but quite often it can lead to counfusion if you are too far off track.

Why are you using RIGHT(Q4,2) in formula 2 to track a 4 digit date?

 

[Carty]

Jason

Thanks again. I'll try to simplify it next time

Q4 is 1932 on sheet A but would only be seen as 32 on sheet B. When I tried stripping the week number from the 4 digit entry it came back as text so I had to force it to be a number by adding +0 so that it was recognised on sheet B

Paul

 

[jasonb75]

In that case, see if this does it

=IF(COUNTIFS(SheetB!E:E,B4,SheetB!O:O,MOD(Q4,100)),"Scheduled","Needs scheduling")

To do it with any other functions you would need to use arrays, you can't combine 2 MATCH functions to find a row where both are the same, it will always return the first individual match for each.

 

[Carty]

I modified your original formula a little:

=IF(COUNTIFS(sheetB!E:E,B4,SheetB!O:O,right(Q4,2)),"Scheduled","Not scheduled") it returned Scheduled. Not sure why but it didn't need the +0 in this instance. I've not seen MOD before but, from the quick test I've just done, works too