Help with formula - show results based on date.

[stefvdm]

Hey everyone, I've been working on a formula for several hours but can't seem to figure it out.

At each end of the month I need a formule in the yellow field that shows the 'available' ID numbers of each person. A

For exemple: For January, it shows that everybody has all of there ID numbers available. But at the end of February, Marleen has used all of her ID numbers (once on 10/02, 15/02 and 20/02). But for Pieter in February he has only used one ID number (on 10/02) and will use his other ID numbers in March and April. But for February the formula needs to detect the 2 available numbers in February.

I really hope you guys can help me figure this out! Thanks in advance!

[oeldere]

Mabye with a pviot table.

See the attached file.

[stefvdm]

Hey Oeldere,

thank you for answering. A table as shown in your file could work, but sadly this solution won't work for my worksheet. I need a way to see a list of the available ID numbers depending on the month (January, February,..). I've been messing with the formula ' vertical search ' but excel only finds 1 ID within the reach A2:A10.

[oeldere]

With index / match (see the green cells).

B20 =IFERROR(INDEX($A$1:$E$10,MATCH($A20&$A$19&B$19,$E$1:$E$10,0),1),"")

G20 =IFERROR(INDEX($A$1:$E$10,MATCH($A20&$F$19&B$19,$E$1:$E$10,0),1),"")

See the attached file.