Using index match to lookup and align data

Hello all,

I have a task which I'm really stuck on how to go about. I've (hopefully) attached an example and what I need to do is take the data from the "raw data tab" and populate it into the "Records tab". The aim being that in the records tab, you can see whether for each year / time period someone handed in data on time or not.


I've filled out how column D in the records tab should look, but I have a HUGE file to do with this with, and can't figure out how I create a formula for this.


Does anyone have any ideas? Or can point me in the right direction?

Many thanks in advance !

Thread title much more descriptive. Thanks for adapting it

Try this array formula, entered with ctrl+shift+enter and dragged down:

Please Login or Register  to view this content.

Does that give you what you're looking for?

Make sure to hold the ctrl and shift buttons and hit enter when committing an array formula. It should end up having curly brackets {} around the whole formula in the formula bar, but don't add those yourself, it won't work.

See attached file based on your example. I'm not seeing any errors. What errors are you seeing?

Ok I just tried it again and think I realise where I was going wrong, I think I wasn't quite referencing the correct bits !

So I think this works a treat !! Thank you so much for this.

Out of interest, what does the "*" mean / do ?