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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,039
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
8,039
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
8,039
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
 

Forum statistics

Threads
1,089,619
Messages
5,409,366
Members
403,260
Latest member
ssauk

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top