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 forum.ozgrid.com/index.php?attachment/69161/

Any help appreciated.

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?

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

Here is a revised spread sheet using your formula:

forum.ozgrid.com/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

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