0

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

enter image description here

Output sheet

enter image description here

UPDATED

enter image description here

Improve this question
7
  • 1
    Your return range: 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.
    – Scott Craner
    Commented Aug 21, 2023 at 16:05
  • MATCH() in range $D21:$D228 , but INDEX() in range $B$10:$B$298 ? What happens if the range sizes are the same - $D21:$D228 and $B21:$B228 (or $D$10:$D$298 and $B$10:$B$298)?
    – JohnSUN
    Commented Aug 21, 2023 at 16:05
  • @ScottCraner See new photo labeled "UPDATE" above. When I do this to your point =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.
    – Paul
    Commented Aug 21, 2023 at 16:10
  • =IF(AND(Workflow!$C15="Secondary",Workflow!$D15=$B$2),Workflow!$B15,"")
    – Scott Craner
    Commented Aug 21, 2023 at 16:16
  • 1
    correct. Just put that in your first cell, assuming row 15 is the first row on the other sheet. Then copy it down.
    – Scott Craner
    Commented Aug 21, 2023 at 16:26

0

Reset to default

You must log in to answer this question.