Lookup start and end value based on date range

[mishaq]

Hello Friends

I desperately need a solution to get handy summary details from a raw data based on provided date range. Appended is requirement.

I have 2 sheets in a workbook namely Summary and Data. Data sheet contains the raw data pertaining to day to day business. I want a Summary sheet which should provide me with the details of my business within a period of date range. I have attached the sample sheet for your reference.

In Summary sheet, if I key “From & To” date in Dispatch Date fields then the below cells should reflect the start & End Order number and also Start & End Invoice number from the Data sheet. I have just manually entered the values to show what I actually need when I key the dates. Please help me with this.

Thanks in Advance
Regards,

[daffodil11]

No problem, we can use LOOKUP for both of these.

A VLOOKUP in its standard form will find the first occurence, and it's output vector is the Starts of each column.

A BigNum LOOKUP will find the last occurence, and its output vector is the Ends of each column.

B10:

=VLOOKUP($D$4,Data!$A$3:$D$33,2,0)

C10:

=LOOKUP(2,1/(Data!$A$3:$A$33<=Summary!$E$4),Data!C3:C33)

D10:

=VLOOKUP($D$4,Data!$A$3:$D$33,4,0)

E10:

=LOOKUP(2,1/(Data!$A$3:$A$33<=Summary!$E$4),Data!E3:E33)

[KrisSquare]

Please check the solved excel file.


I had to remove the "-" in the order numbers to get the max and min values. Let me know if "-" is crucial so that we can think of an alternative

Indirect is used to make the range dynamic so that even when your source range increase you are good to go. No need to change the range

[mishaq]

Hello daffodil11

I would like to thank you for the response. The formulaes you have provided to put in cells C10 & E10 works great. However formulaes for B10 & D10 does not fulfill my requirement. These cells should also have the similar formulaes like C10 & E10. I have just changed the data and able to get appropriate values in C10 & E10 however not in B10 & D10. Please re-view the attached data file with modified values.

Thank you

[mishaq]

Hello Guys

Could you please look into my query and the latest attached file and suggest the solution for it.

Thank you

[bebo021999]

Try in B10:

Please Login or Register  to view this content.

D10:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.