Common entry

Iron Contributor

Dear Experts,

                     I need to prepare a list of rnti's in column "N" ( a parameter say) which is common in all these 4 Tables,


may be with XMATCH , or any other formulae..



Thanks in Advance,



6 Replies


In your example, there are no common values, but if there were, they would be returned by


=FILTER(Table7[rnti-BIP], COUNTIF(Table1[[CRNTI]:[CRNTI3]], Table7[rnti-BIP])*COUNTIF(Table4[rnti-UL-TTI], Table7[rnti-BIP])*COUNTIF(Table5[rnti-DL-TTI], Table7[rnti-BIP]), "")

Thanks @Hans Vogelaar it really helps, once you have some bandwidth , can you please share one more logic /Formula apart form the Filter function..


Thanks & Regards

Anupam Shrivastava

Hi@Hans Vogelaar seems a bit Lengthy..


A formula avoiding FILTER would probably be much longer...


If you have the FILTER function I suggest you use it!  An alternative might be UNIQUE:

= LET(
    list,     SORT(TOCOL(data,1)),
    distinct, UNIQUE(list),
    unique,   UNIQUE(list,,TRUE),
    combined, VSTACK(distinct, unique),

What this does is bring all your data into a single sorted column.  It then calculates every distinct value and appends the list of those occurring only once.  Duplicates in the original list occur only once in the combined list and are easily identified using the final application of UNIQUE.