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

Carty

Board Regular
Joined
Mar 3, 2009
Messages
76
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
 

Some videos you may like

Excel Facts

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,930
Office Version
2019
Platform
Windows
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
Joined
Mar 3, 2009
Messages
76
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

Well-known Member
Joined
Dec 30, 2008
Messages
9,930
Office Version
2019
Platform
Windows
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
Joined
Mar 3, 2009
Messages
76
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
Joined
Dec 30, 2008
Messages
9,930
Office Version
2019
Platform
Windows
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
Joined
Mar 3, 2009
Messages
76
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top