MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
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!