• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

index/match possibly?

zip77

New Member
I think index/match may be the answer to my question but I haven't been able to make it work.
What I'm trying to do:
I'm validating jobs exist at the correct time for sales.

Sheet 1 Col A: Sales part number
Sheet 1 Col B: Sales due date
Sheet 2 Col A: job part number
Sheet 2 Col B: job due date
Sheet 2 Col C: Job number. This is what I need to return.

I need to match on Sales part numbers and job part numbers AND sales due date and job due date. The twist is sales due date and job due date aren't always the same date. Job due date is usually a week or two before the sales due date.

sheet 1 col A= sheet 2 col A
and
sheet 1 col B <= sheet 2 col B (closest match)
Return Sheet 2 col C

Additional info: Data on both sheets are in a table that come from an ODBC query. No sorting applied, if possible would prefer to keep it that way - the user will be sorting so trying to avoid a formula that requires it.

Appreciate any guidance.
 
A workbook would have helped!
Maybe this is where you should go out and buy Office 365.

To filter out dates with the wrong Part Number you could use the array formula
= IF( Jobs[PartNumber]=Sales[@PartNumber], Jobs[DueDate] )
[This would need CSE, dynamic arrays or the use of a Named Formula (a formula defined in the refers to box of Name Manager)]
To find an exact match or the next larger is best achieved with a new function XLOOKUP:
= XLOOKUP( Sales[@DueDate], IF( Jobs[PartNumber]=Sales[@PartNumber], Jobs[DueDate] ), Jobs, , 1 )

Something similar is possible with LOOKUP except that requires sorted dates.
= LOOKUP( Sales[@DueDate] + 14, Jobs[DueDate] / ( Jobs[PartNumber]=Sales[@PartNumber] ), Jobs[JobNumber] )

At this point, I guess you are wishing you hadn't asked!
 
I have office 365 and have been playing with the xlookup function.
sample data attached.
 

Attachments

  • SO_to_WO_example.xlsx
    12.7 KB · Views: 9
64943

In E2, array (Ctrl+Shift+Enter) formula copied down :

=LOOKUP(1,0/FREQUENCY(-[@[CUST_DUE_DATE]],IF(Table2[CUSTOMER_PO]=[@[CUSTOMER_PO]],-Table2[DATE_DUE],0)),Table2[JOB])

Regards
Bosco
 
This is your workbook with the XLOOKUP formula implemented. The first time I looked I did not fully understand whether the job was initiated once a sale had been made or, conversely, whether the sale could only be completed following the job.
I have sorted the Job sheet differently to demonstrate that the answers do not depend upon the sort order.
I have also gone part way to implementing Bosco's solution but, if any explanation is needed, I will leave it to him!
 

Attachments

  • SO_to_WO_example.xlsx
    20.4 KB · Views: 5
Greatly appreciate it guys. That got me very close. The problem that came up is repeat orders all show the same job number. Unfortunately there are no other unique fields, Sales order, part number, qty, customer po etc are all the same. Only difference would be the cust_due_date.

Would it be possible to add an AND in the IF statement?
(my formatting wont be correct here)
= XLOOKUP( [@[CUST_DUE_DATE]], IF( Jobs[PART]=[@PART] AND Jobs[Date_DUE] within 14 days previous of [@CUST_DUE_DATE]] , Jobs[DATE_DUE] ), Jobs[JOB], "No job", -1 )

Without any other unique columns I'm not sure how else to try.
 
The formula similar to the one I gave can return the Job due date
= XLOOKUP( [@[CUST_DUE_DATE]], IF( Jobs[PART]=[@PART], Jobs[DATE_DUE] ), Jobs[DATE_DUE], "No job", -1 )
You can test this against the customer due date to determine whether it is sufficiently recent to be relevant.
Alternatively you could apply the date test first and only search over dates that are within scope. This can return the associated job number or an error message if not found.
= XLOOKUP( [@[CUST_DUE_DATE]],
IF( (Jobs[PART]=[@PART]) * (Jobs[DATE_DUE] > [@[CUST_DUE_DATE]] - 7), Jobs[DATE_DUE] ),
Jobs[JOB],
"No job",
-1 )
 

Attachments

  • SO_to_WO_example.xlsx
    20.6 KB · Views: 7
Peter,
For the second formula. Wont that only return the job if the job due date is exactly cust_due_date -7 instead of between cust_due_date and cust_due_date -7?
 
I think it only eliminates jobs that completed 7 or more days before the customer due date; I started with 14 but that made no difference with the current dataset.
 
Back
Top