Copy value from any position in INDEX MATCH function

[musungujim]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID number[/TD]
[TD]Form Submitted[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]ID 001[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]ID 002[/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]ID 003
[/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]ID 001[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]ID 003[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

I've been using VLOOKUP and INDEX MATCH to relate ID number to form submission. If I enter "Yes" in the first instance of an ID number, there is no problem and "Yes" copies to all subsequent times the ID number shows up (see ID 001). The problem I've been having is that if I enter "Yes" on the second instance of the ID number or later, it does not change the corresponding or previous "No" to "Yes" (see ID 003).

I've tried using several different methods to no success. My latest idea was to use SMALL to create a subset based on MATCH values and use OFFSET to skip the first value if blank. It still doesn't do what it's supposed to and is basically an over complicated INDEX MATCH function.

=IF(INDEX(C:K,SMALL(MATCH(C2,C:C,0),1),9)="Yes","Yes",IF(INDEX(C:K,SMALL(OFFSET(C2,MATCH(C2,C:C,0),0,2500,1),1),9)="Yes","Yes","No"))

Is there any way to get this to work properly?

Thanks.

 

[admiral100]

Hi,

Try this

Crtl+Shift+Enter NOT just Enter

C2 and drag down =IFERROR(INDEX($B$2:$B$6,MATCH(1,IF($A$2:$A$6=A2,IF($B$2:$B$6="Yes",)))),"No")

A B C
[TABLE="width: 254"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]ID number[/TD]
[TD="align: left"]Form Submitted[/TD]
[TD="align: left"]Received[/TD]
[/TR]
[TR]
[TD="align: left"]ID 001[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: left"]Yes[/TD]
[/TR]
[TR]
[TD="align: left"]ID 002[/TD]
[TD][/TD]
[TD="align: left"]No[/TD]
[/TR]
[TR]
[TD="align: left"]ID 003[/TD]
[TD][/TD]
[TD="align: left"]Yes[/TD]
[/TR]
[TR]
[TD="align: left"]ID 001[/TD]
[TD][/TD]
[TD="align: left"]Yes[/TD]
[/TR]
[TR]
[TD="align: left"]ID 003[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: left"]Yes[/TD]
[/TR]
</tbody>[/TABLE]

 

[musungujim]

It works! Brilliant, thank you!