If ANY matching ID numbers have Yes then value = Yes

Hi,

I need a little help with something please. I have the following data on 2 sheets of my excel document:

Sheet1:

Please Login or Register  to view this content.

Sheet2:

Please Login or Register  to view this content.

RTM is currently blank on Sheet1. Im trying to work out a formula so it searches for the account number on Sheet2 and if any of the same account numbers has ATO as the value as Yes then the value on Sheet1 will be Yes. As there are multiple records of the name account number on Sheer2 (each record meaning something different) I'm not sure how to do this, if it was just a single record then id do a IF ISNA and Vlookup formula, but because theres multiple records with the same same account number im not sure how to do this.

So for example 9223585, even though theres 2 records for the account number on Sheet2 it would return Yes as one of those records has the value of Yes on the ATO.

Any help would be great please.

Thanks

Hi & welcome to the board.
How about
=IF(ISERROR(XMATCH(A2&"|Yes",Sheet2!$A$2:$A$9&"|"&Sheet2!$B$2:$B$9,0)),"","Yes")

Thank you very much, its very much appreciated as that's worked a treat.

You're welcome & thanks for the feedback