excelexcel-formulaexcel-2010

Consecutive index/match issue


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.

enter image description here


Solution

  • =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