Please help me find the missing number and name assigned to the number for data from sheet 2 (attached example). It might have been easier to do a Vlookup, but the data cells' numbers are separated by "|" which cant be changed. I might need a complex formula or vba formula which i am happy to work with. Thank you
dont play hard just play right !!!
Clean all expected result.
B2
=LET( a,FILTER(A2:A1000,A2:A1000<>""), b,Sheet2!A1:A1000, c,Sheet2!B1:B1000, d, MAP(a,LAMBDA(x,IF(SUMPRODUCT(--ISNUMBER(SEARCH(x,b&"")))>0,"","Missing"))), e, MAP(a,LAMBDA(x,IFERROR(INDEX(c,1/(1/(MAX(--ISNUMBER(SEARCH(x,b&""))*ROW(b)))))&"",""))), HSTACK(d,e) )
If all numbers are 4 digit
=LET(v,VLOOKUP("*"&A2:A165&"*",Sheet2!A4:B50&"",2,),HSTACK(IF(ISNA(v),"Missing",""),IFNA(v,"")))
If not
=LET(v,XLOOKUP("*|"&A2:A165&"|*","|"&Sheet2!A4:A90&"|",Sheet2!B4:B90&"",,2),HSTACK(IF(ISNA(v),"Missing",""),IFNA(v,"")))
Another options:
put on column C and copied down:
=TRIM(IFERROR(LOOKUP(2^15;SEARCH(A2;Sheet2!$A$4:$A$50);Sheet2!$B$4:$B$50);""))
in Column B and copied down:
=IF(LEN(C2)>0;"";"Missing")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks