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

#### Carty

##### Board Regular
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 :
Code:
``1 =MATCH(B4,SheetB"E:E",0)``
Resolves to 12
To check the due date:
Code:
``2 =MATCH(RIGHT(Q4,2)+0,SheetB”O:O”,0)``
Resolves to 21

When I use
Code:
``=IF(1=2,”Scheduled”,”Needs scheduling”)``
returns Needs scheduling – correctly (12 doesn’t equal 21)
When I use
Code:
``=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

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### jasonb75

##### Well-known Member
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

##### Board Regular
Jason

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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
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

1,102,162
Messages
5,485,113
Members
407,481
Latest member
junniec

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...