• 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.

Struggling with a formula

G

Gatisha

New Member
Hi,
I am struggling with a formula, I want a formula so that for following case:
I have SO and Dates in file1 and SO, start dates and end dates in file2, I want to map the Dates in file 1 with the range of dates in file2 so that Date of file1 lies between start date and end date in file2 with same SO
Can you please help me with this?
 
If we don't care where the date lies within the range, could you do

=VLOOKUP(A2, 'File 2 sheet'!A:C, 2, 0)+1

which should give you the date 1 after start date?
 
No this can't be, firstly We have to map the SO in file1 with the SO in file2 then if true we will see if Date in file1 lies between Start Date and End Date then if True we want that Date of file1 in a column of file2
 
Hi ,

Upload your workbook with enough data in it , and manually enter a few outputs , so that we can cross-check the result of the formula.

Narayan
 
Please check the following file.
The Result has to first match the SO in File1 with the SO in File2 and then provide the minimum Date of File1 that lies between Start Date and End Date of File2 where Date of file1 is >= (More than or equal to) Start Date of File2 but < (less than) End Date of File2 provided they both have the same SO
 

Attachments

  • Sample Data.xlsx
    14.8 KB · Views: 1
Hi ,

Why is the date format in the File2 tab different from that in the File1 tab ?

Can we make it identical to what is in File1 ?

Narayan
 
I checked it, this is returning the correct value for all, Thank you so much for the help. I am really grateful, I was struggling with it for a while now.
 
Hi ,

The formula is :

=SMALL(IF(SOnumbers=B3,IF(Dates>=C3,IF(Dates<D3,Dates))),1)

Here Dates is a named range , referring to :

=File1!$C$2:$C$242

SOnumbers is a named range , referring to :

=File1!$B$2:$B$242

Please note the changed formula , since an earlier date can occur in a later row.

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan
 
You must log in or register to reply here.
Back
Top