Forum Discussion
VLookup or equivalent
Hi,
I think I am being a little dense here. Please forgive me.
I have two worksheets. One (Call it Other) with a series of numbers (e.g. 10 random numbers between 1 and 1000) that are related to the another worksheet (call it Master) that has all the numbers (e.g. 1 through 1000). I want to look up what numbers in Master that do not appear in Other.
When I do a VLookup in a new worksheet called Results, it keeps giving me the numbers in Other, not in Master. I want the opposite (I did try swapping things around to no avail). I want to know the numbers in Master that are not in Other.
Can anyone help with this or suggest an alternative?
Thank you,
Ken
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
5 Replies
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 Faraz,
Thank you. This worked. I did not "Not Found" formula to identify which ones did not match. Then I was able to compare the results with the larger file, copied them results as text and then delete the ones that matched.
Thank you!
Ken
Hi KenPeterson
If I read correct are you like to know how I get "Not Found" in the data ?
there is no formula "Not Found" but we use IFERROR and replace the error value with our desired text or another function
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
