1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Lookup multiple criteria

Discussion in 'Ask an Excel Question' started by Thomas Kuriakose, Dec 21, 2017.

  1. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    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

    Attached Files:

  2. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    186
    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

    Attached Files:

  3. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    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
  4. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    186
    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
  5. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    186
    Dear Thomas,

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

    regards
    naresh
  6. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    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

    Attached Files:

  7. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    186
    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

    Attached Files:

    Thomas Kuriakose likes this.
  8. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    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
  9. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    Hi, Thomas,

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

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

    upload_2017-12-21_18-18-5.png

    Then, which one is the desired result?

    Regards
    Bosco
  10. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    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
  11. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    Hi,

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

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  12. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    Respected Sir,

    Amazing. This works perfectly.

    Thank you very much for your support and solution provided.

    Much much appreciated.

    with regards,
    thomas
  13. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    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.
    upload_2017-12-21_16-54-52.png

    Case2: The below numbers do not have dates in 2017 for the corresponding dates of 2018, can this be made blank.
    upload_2017-12-21_16-55-8.png

    Thank you very much once again,

    with regards,
    thomas
  14. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    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
  15. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    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

    Attached Files:

  16. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    Please check with the attached revise file

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  17. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    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:
    upload_2017-12-22_18-14-23.png

    Map2:
    upload_2017-12-22_18-15-1.png

    The version we are using is 2016.

    Thank you very much,

    with regards,
    thomas
  18. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    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

    Attached Files:

    Last edited: Dec 22, 2017
    Thomas Kuriakose likes this.
  19. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    Respected Sir,

    Thank you very much for the revised file once again.

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

    upload_2017-12-22_20-21-53.png

    upload_2017-12-22_20-22-31.png

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

    thank you very much,

    with regards,
    thomas
  20. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    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

    Attached Files:

    Last edited: Dec 23, 2017
  21. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    721
    Respected Sir,

    Thank you very much for the final revision.

    Much much appreciated,

    with regards,
    thomas

Share This Page