I am attempting to use index/match in conjunction with an if/and statement that does the following.
If column C = Secondary
AND if column D = Information/Videos
Take value of column B and output it.
This is not what is happening. The only case it is working is for the first occurrence of it. See the reference sheet and output sheets below. In the reference sheet, I labeled the rows/values in which should be appearing and labelled the respective row. Then look at the output sheet. The labelled row 1 is correct. However, after that the outputs are not correct or recognizing the rows that should pull over.
Does anyone see what is wrong?
Formula
=IF(AND(Workflow!$C26="Secondary",Workflow!$D26=$B$2),INDEX(Workflow!$B$10:$B$298,MATCH($B$2,Workflow!$D21:$D228,0)),"")
Reference sheet
Output sheet
UPDATED
Workflow!$B$10:$B$298
is out of sync with your search range:Workflow!$D21:$D228
Match returns the RELATIVE row, not the ACTUAL row to the INDEX. Make them both ranges start and end on the same row.=IF(AND(Workflow!$C15="Secondary",Workflow!$D15=$B$2),INDEX(Workflow!$B10:$B298,MATCH($B$2,Workflow!$D10:$D217,0)),"")
the range still outputs the incorrect values. Basically what you saw in my initial was I had absolute value on one and dragged down the formula.=IF(AND(Workflow!$C15="Secondary",Workflow!$D15=$B$2),Workflow!$B15,"")