Forum Discussion
VLookup or equivalent
hi KenPeterson
Please see the attached solution file, You need to compare your master sheet with random numbers where ever the values are not found you will have the result #NA, you can simply wrap them into IFERROR. Construction of the formula will look as below
=IFERROR(VLOOKUP(A2,random!$A$2:$A$11,1,FALSE),"Not Found")With the help of the filter you can just select the values "Not Found"
If you have Office365 with new dynamic arrays then filter function can handle it very easily
=FILTER($A$2:$A$1001,NOT(ISNUMBER(MATCH(MasterSheet!$A$2:$A$1001,random!$A$2:$A$11,0))))and if you wish to join the text then use TextJoin Function
=TEXTJOIN(",",,FILTER($A$2:$A$1001,NOT(ISNUMBER(MATCH(MasterSheet!$A$2:$A$1001,random!$A$2:$A$11,0)))))Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
hi KenPeterson
Please see the attached solution file, You need to compare your master sheet with random numbers where ever the values are not found you will have the result #NA, you can simply wrap them into IFERROR. Construction of the formula will look as below
=IFERROR(VLOOKUP(A2,random!$A$2:$A$11,1,FALSE),"Not Found")
With the help of the filter you can just select the values "Not Found"
If you have Office365 with new dynamic arrays then filter function can handle it very easily
=FILTER($A$2:$A$1001,NOT(ISNUMBER(MATCH(MasterSheet!$A$2:$A$1001,random!$A$2:$A$11,0))))
and if you wish to join the text then use TextJoin Function
=TEXTJOIN(",",,FILTER($A$2:$A$1001,NOT(ISNUMBER(MATCH(MasterSheet!$A$2:$A$1001,random!$A$2:$A$11,0)))))
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert