Copy value from any position in INDEX MATCH function

[musungujim]

ID number	Form Submitted	Received
ID 001	Yes	Yes
ID 002		No
ID 003		No
ID 001		Yes
ID 003	Yes	No

<tbody>
</tbody>

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

ID number	Form Submitted	Received
ID 001	Yes	Yes
ID 002		No
ID 003		Yes
ID 001		Yes
ID 003	Yes	Yes

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[musungujim]

It works! Brilliant, thank you!