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

      • ExcelExciting's avatar
        ExcelExciting
        MVP
        Mar 10, 2020

        KenPeterson wonderful... Glad to hear you query was resolved 🙂

Resources