vlookup cell values only if date on adjacent column less than requested. INDEX/MATCH?

  • Problem:


    - This is a spread sheet that records numbers chosen for a lottery bonus ball. The spread sheet is a very simplified version of the one I am working with however I have extracted the problem I am encountering.
    - The master spread sheet will allow me to return many different coding values to build a webpage based on the data within. I am however; stuck on a solution to this issue. I would prefer to use a formula solution as VBA is not good for Mac Excel.
    - Column C checks the chosen number in Column B against a range of numbers recorded as drawn on the Draws sheet. If it finds them it marks Column 'C' with a Y.


    Q: My conundrum is, I would like the Y to be marked only if the number matching was drawn after the registration date in the draws column.


    You can download my spread sheet here ozgrid.com/forum/core/index.php?attachment/69161/


    Any help appreciated.

    Files

    • example.xlsx

      (22.87 kB, downloaded 57 times, last: )
  • Re: vlookup cell values only if date on adjacent column less than requested. INDEX/MA


    Try this...


    On Lines Sheet,
    In C2

    Code
    1. =IF(ISNA(MATCH(B2,drawn,0)),"",IF(INDEX(drawdates,MATCH(B2,drawn,0))>E2,"Y",""))


    and then copy down.

    Regards.
    sktneer

  • Re: vlookup cell values only if date on adjacent column less than requested. INDEX/MA


    That seems to work, thank you everso much, I shall try it in my master spread sheet now and let you know!


    Not quite working yet, see my next post?

  • Re: vlookup cell values only if date on adjacent column less than requested. INDEX/MA


    Sadly this doesn't work. It doesn't check the whole range of the drawn table only the top value.


    For example if I enter a draw matching 33 for a day before the registration date it shows as undrawn (this is correct)
    But if I then enter a draw matching 33 for the day after the registration date it still shows as undrawn (this is incorrect as it was drawn after registration)


    Any help?

  • Re: vlookup cell values only if date on adjacent column less than requested. INDEX/MA


    Upload the workbook with some inputs on both the sheet and let me know what's wrong there.

    Regards.
    sktneer

  • vlookup cell values only if date on adjacent column <= requested. INDEX/MATCH


    Here is a revised spread sheet using your formula:


    ozgrid.com/forum/core/index.php?attachment/69166/


    As you will read from my notes in the spread sheet, the formula works so long as the number drawn only appears once.
    This won't happen there are repeat draws quite often in the lottery.
    If I remove the multiple entry and ensure all of the drawn numbers are correct the formula works very well thank you.
    Still without the fix for an issue of multiple numbers I cannot use it. It would be nice to have '<=' as well, is this just a case of adding the '='?

  • Re: vlookup cell values only if date on adjacent column less than requested. INDEX/MA


    Okay try this....


    In C2

    Code
    1. =IF(ISNA(MATCH(B2,drawn,0)),"",IF(MAX(INDEX((drawn=B2)*drawdates,0))>E2,"Y",""))

    Regards.
    sktneer

  • Re: vlookup cell values only if date on adjacent column less than requested. INDEX/MA


    You've only gone and done it!!!! Thank you so so much! I owe you one ;) I shall try it in my master now and report back

  • Re: vlookup cell values only if date on adjacent column less than requested. INDEX/MA


    Quote from Elusive;771013

    You've only gone and done it!!!! Thank you so so much! I owe you one ;) I shall try it in my master now and report back


    This solution works really well thank you everso much. How do I merge all cells in a column without painstakingly typing in =Q1&Q2&Q3... etc

  • Re: vlookup cell values only if date on adjacent column less than requested. INDEX/MA


    You're welcome Elusive!


    Would be better if you open a new thread with your new question.
    Moreover if you need to do that in a single cell, there is a workaround to achieve this. But if you need to do this for multiple cells, you will need a VBA solution so better you open the new thread in the correct sub-forum as per your requirement.

    Regards.
    sktneer