Lookup multiple criteria

[Thomas Kuriakose]

Respected Sirs,

We have the following data with three tabs in the attached file -

1. Tab1 - Map1 - Data to be entered after lookup from Plan tab
2. Tab2 - Map2 - Data to be entered after lookup from Map1 tab
3. Tab3 - Plan - Has the source data

1. The plan tab has numbers, from and to date and Value which fall in different years. For Map1 tab we need to lookup the number in Plan tab and select the dates from (column B) and to (column C) which falls in the year 2017 and 2018 only and the corresponding Value from Column D and enter in Map1 tab in column B,E and F respectively.

2. The dates from (column B)and to (column E) entered in Map1 tab now needs to be matched number wise and entered in Map2 tab in column B and C. The end date of 2017 and star of 2018 has been entered number wise. The date format should be 'ddmmyyyy.

I have entered the values manually for the first two numbers in Map1 and Map2.

Kindly guide on how to get this working.

Thank you very much,

with regards,
thomas

 

[Naresh Yadav]

Hi,

Pls check if this works for you

=INDEX(Sheet1!$A$2:$R$67,,MATCH(A$1,Sheet1!$A$1:$R$1,0))
regards
Naresh

 

[Thomas Kuriakose]

Respected Sir,

Thank you for your support, is the attached file a sample for the requested solution.

As the data does not match with the requirement.

Kindly confirm,

thank you very much,

wit regards,
thomas

 

[Naresh Yadav]

Dear Thomas,

Yes,the attached file is a sample for the requested solution.
Pls let me know where the data is not matching with your requirement in my attached excel sheet so that it could be eased for me to understand

I also urge you to please address me with my name..
I would feel much better than calling myself Sir..

regards
Naresh

 

[Naresh Yadav]

Dear Thomas,

Sorry and ignore all my Post,I have sent you wrong excel sheet which is not pertaining to your query

regards
naresh

 

[Thomas Kuriakose]

Dear Naresh,

Kindly find attached the file with the formula proposed. This is used only in tab Map1 tab.

Could not use the same in Map2 tab.

Thank you very much,

with regards,
thomas

 

[Naresh Yadav]

Dear Thomas,

I have tried to solve your 1st Query, Pls check and suggest if it is correct for you

regarding your 2nd query, I am sorry i couldn't understand
hope someone else in the forum will come and help you to sort out your issue

regards
Naresh

 

[Thomas Kuriakose]

Dear Naresh,

Thank you very much for your support.

The 1st query values are correct.

Thank you very much for your support on this.

much appreciated,

with regards,
thomas

 

[bosco_yip]

Hi, Thomas,

In "Plan Sheet" , some Numbers have different Value in Col D.

For example, in Number. 32999154 have 2 values, 7000 and 3500



Then, which one is the desired result?

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for your support.

Yes, there are two values for the same number. In this example (row 906) we have to only consider the first value of 7000 as the dates fall between two years 2017 and 2018.

The requirement is to split the dates only for numbers which fall between two years 2017 and 2018.

The next number (row 907) falls in year 2018 and need not be considered.

Thank you once again.

with regards,
thomas

 

[bosco_yip]

Hi,

Please check attached file in formula solution for "Map1" and "Map2" Sheets

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Amazing. This works perfectly.

Thank you very much for your support and solution provided.

Much much appreciated.

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

One small change for the below -

Case1: The below numbers do not have dates in 2018 for the corresponding dates of 2017, can this be made blank.


Case2: The below numbers do not have dates in 2017 for the corresponding dates of 2018, can this be made blank.


Thank you very much once again,

with regards,
thomas

 

[bosco_yip]

Hi,

This is a result sheet, how about the source data sheet?

So, upload a revised file to show the revised source data and the expected result.

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Apologies for the late reply. The file is the same.

Kindly find attached the file with the data.

Thank you very much once again,

with regards,
thomas

 

[bosco_yip]

Respected Sir,

Apologies for the late reply. The file is the same.

Kindly find attached the file with the data.

Thank you very much once again,

with regards,
thomas

Please check with the attached revise file

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the revised file.

Sir, when open the file and i enable editing the values in Map1 and Map2 disappear.

If the file is opened without enable editing the values are there.

I am not able to understand why this is happening.
Map1:


Map2:


The version we are using is 2016.

Thank you very much,

with regards,
thomas

 

[bosco_yip]

Hi,

I know the problem, my computer date setting using UK while your one is US.

I herein attach again the revised file, the formula use DATE() instead, to aviod the mistake.

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the revised file once again.

Now only the first row is getting updated on clicking enable editing.





I am not able to figure out why this is happening,

thank you very much,

with regards,
thomas

 

[bosco_yip]

Hi,

1] Sorry, I haven't double click to copy down the revised formula in "Map1 Sheet" Col C.

2] In "Map2 Sheet" Col B and Col C, please change the Custom Cell Formatting to mmddyyy to meet with your system if required.

3] I herein re-uploaded the file again.

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the final revision.

Much much appreciated,

with regards,
thomas