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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top