Timeline Vlookup

[Hiren Vakta]

Hi,

I am creating Salesman-wise Sales Report to review the performance of salesmen.

For that I export sales data from my accounting software (tally ERP 9.0) to excel which is register of date-wise & bill-wise sales transactions.

I have a master data file with information of Assigned Salesmen to our customers.

By using Vlookup I add column of salesman beside the customer name in above exported file.

However, now a days... management adds more salesmen and re-assigns them to customers.

Hence, I need to find current effective salesman to the customer for the particular sales transaction by vlookup.

Example :

If from 01-Apr-2015 : for Customer "Xyz" - Salesman is "abc" and from 01-May-2015 the sales changed to "pqr".

Please guide to how to find appropriate assigned salesman for customers?

Please find sample file for this.

Thanks.

 

[Jake Collins]

Dear Hiren Vakta

Changing the formula in the sales register G4 to

=VLOOKUP($D3,'Customer Master'!$A$1:$F$95,MATCH(A3,'Customer Master'!$A$2:$F$2),FALSE)

and then copying this down, should give you your desired behaviour.

 

[Hiren Vakta]

Hi,

dates in header is wef so it is time slot... and my desired date may in between the dates.. so MATCH will not function able here...

 

[Chihiro]

So what is the slot for 01/Apr/14 wef?
Anything prior to 01/Apr/14? Or from 01/Apr/14 up until 15/Jan/16?

FYI - w.e.f is British Eng and US Eng users will probably not know what it means (I had to look it up )

 

[Hiren Vakta]

Hi,

Yup, these dates are "with effect from" (w.e.f.) i.e. for first table from 01-Apr-2015 to 14-Jan-16, for 2nd table from 15-Jan-16 to 28-Jan-16 and for third table from 29-Jan-16 to till date.

Thanks for guiding me .

 

[Hiren Vakta]

Where is Sir Hui...?

Please help me out... I hope you are working on it..

Let me know if additional information required for clear understanding..