I haven't seen any post with somenthing I could use, so I hope it is not repeated.
I'm working on a Excel 2010 sheet that should look for a date (row 3), cross it with a marked cell (coded tv) and, finally, find a name in column A.
The goal is to select a date and find which name has the code "TV" on that day.
The formula in the image is the one I'm working on right now. It is a modification from another I found here.
INDEX(INDEX($A$4:$A$37;0;MATCH(B39;$P$3:$BW$3;0));MATCH($F$42;INDEX(P:BW;0;MATCH(B39;$P$3:$BW$3;0)-1);0))
This is the formula ready to use (and translated to english excel), but doesn't work. It gives me a #ref! error on the first INDEX. I believe that it is a problem with the order of commands, but I should admit that it is over my programming knowledge.
My older version of this formula is able to do something similar (it finds a name), but it doesn't care about the tag "TV".
=INDEX($A$4:$A$37;MATCH(B39;$P$3:$BW$3;0);MATCH($F$42;P4:P37;0))
I have a primitive version of this formula that works, but it is column related and quite slow to update the whole calendar
=INDEX('2024'!$A$4:$A$37;MATCH("TV";'2024'!X$4:X$37;0))
The one I'm looking for should be able to find the person for any given day, by itself.
=INDEX($A$3:$A$39,MATCH("TV",INDEX($P$3:$BW$39,,MATCH($B$1,$P$3:$BW$3,0)),0),)
First this checks for the column:
INDEX($P$3:$BW$39,,MATCH($B$1,$P$3:$BW$3,0)),0),)
Next this is wrapped in another index/match, where the column A values are indexed and your indexed column is matched against TV