I have two worksheets in a workbook.

In reg sheet I have a list of reference numbers (Column A)
In the app sheet I have a list of reference numbers (Column A) and list of related statuses (Column B)
See images

In the reg sheet I want to record if the reference number from Column A is present in the list of reference numbers if one of the statuses is true.

See the example below – I want a yes, in Column B in the reg sheet, then the ref number is in the app sheet and the associated status is NEW or RENEW

What formula do I want in column B to get it?

TIA

reg sheet (before)
Column A Column B
ref number
406768/260/15551
145171/260/22580
407065/260/17141
405494/260/05421
406141/260/09471
405929/260/08251
406396/260/10261
406350/260/10371
406344/260/10401
406191/260/09281
406783/260/15551
145172/260/22580
406173/260/09261

app sheet
Column A Column B
ref number status
406768/260/15551 NEW
405494/260/05421 UPDATE
406141/260/09471 RENEW
405929/260/08251 NEW
406396/260/10261 MIGRATE
406350/260/10371 NEW
406173/260/09261 UPDATE

reg sheet (after)
Column A Column B
ref number in apps
406768/260/15551 YES
145171/260/22580 NO
407065/260/17141 NO
405494/260/05421 NO
406141/260/09471 YES
405929/260/08251 YES
406396/260/10261 NO
406350/260/10371 YES
406344/260/10401 NO
406191/260/09281 NO
406783/260/15551 NO
145172/260/22580 NO
406173/260/09261 NO

I can probably bodge something with a multipart VLOOKUP, but I’m sure there’s a more elegant solution out there.

Match and Index?

A countif should work for you?

On phone so syntax may be wrong but something like:
If(countif(app!b:b,a2)>0, “YES”,”NO”)

Or whichever columns it’s in

lots of options

on app sheet add another column with YES/NO in it

Something like

=if(and(not (iserror (match(reg cell, app sheet list, 0)), or (app status=new, app status =renew)), yes, no)

Something like this (in reg column b)

=if(and(not (iserror (match(reg cell, app sheet list, 0)), or (app status=new, app status =renew)), yes, no)

Thanks guys. Sorted!