Index and Match multiple rows - only returning top row

Hi there,

I'm using an Index and Match function to search a dataset and return various values based upon one horizontal value (i.e. column B), and one vertical value (row 2). [Very simplified version of data is shown in a picture below]

The unique ID (i.e. column B) in some cases appears more than once in the dataset (highlighted in pink) and therefore has multiple rows in the dataset.

When requesting a value for an ID with multiple rows, it only returns the value found in the first relevant row - in the example shown a "no" - see orange highlighted cells.

However, what I need to do sometimes is overwrite that "no" with a "yes" if a yes is present in one of the other rows.

Any ideas of how to do this most easily? Thanks for your help.

Picture1.jpg