Forum Discussion

KenPeterson's avatar
KenPeterson
Copper Contributor
Mar 10, 2020
Solved

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 relat...
Formulas and Functions
  • ExcelExciting's avatar
    ExcelExciting
    Mar 10, 2020

    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

    If you find the above solution resolved your query don't forget mark as Official Answer.

ExcelExciting's avatar
ExcelExciting
MVP
Mar 10, 2020

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

If you find the above solution resolved your query don't forget mark as Official Answer.

  • KenPeterson's avatar
    KenPeterson
    Copper Contributor
    Mar 10, 2020

    ExcelExciting 

     

    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

    • ExcelExciting's avatar
      ExcelExciting
      MVP
      Mar 10, 2020

      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

      If you find the above solution resolved your query don't forget mark as Official Answer.

       

      • KenPeterson's avatar
        KenPeterson
        Copper Contributor
        Mar 10, 2020

        ExcelExciting 

         

        Hi,

         

        I apologize for the confusion.  I was able to figure it because of the term.  Any where the result came up with Not Found I was able to determine that that matched a number I was looking for, then I did an if statement to find the results I needed.

         

        Thanks again.

        Ken

Resources