Forum Discussion

Jolange's avatar
Jolange
Copper Contributor
Aug 28, 2020

checking postcode is on a list formula

I am trying to get a formula that will check an entry in column E is on a list and answer yes or no.

This is a form where a postcode will be entered and if it is on a list of 3190 entries held on "Data1" sheet will enter yes into this cell. Can you help? I am pretty new to formulas an struggling. Thanks.

7 Replies

    • Jolange's avatar
      Jolange
      Copper Contributor

      Hi

      Riny_van_Eekelen Thank you,  got this one to work but now i am getting all false results even when i type the top item from the list, any ideas how to get true? I have even copied and pasted from the list to get it exact but still get false.

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        Jolange Can't tell without seeing. I suspect that you are not finding exact matches due to trailing or leading spaces. What if you copy the first item of the list to the cell that should be matched?

         

         

  • Bennadeau's avatar
    Bennadeau
    Iron Contributor

    Hi Jolange,

     

    You can use this formula...

    =NOT(ISERROR(MATCH(A1,Sheet2!A:A,0)))

     

    In this example, I added entries A to H in sheet2 column A. Then, (what you see in the screen shot) in sheet1 if the value of cell A1 is found in Sheet2 column A, cell B1 returns "true". otherwise, it returns "false".

    • Jolange's avatar
      Jolange
      Copper Contributor

      Bennadeau Thank you this is great but now i can only get a false result not a true even if i copy and paste somthing from the list, any suggestions please? Formula as i have it is

      =ISNUMBER(MATCH(F2Sheet2!BI2:Sheet2!D3190,0))

      I was using YO126EY as a test and this is exactly how it is in the list i was given.

      • Bennadeau's avatar
        Bennadeau
        Iron Contributor

        Hi Jolange,

         

        This formula: =ISNUMBER(MATCH(F2Sheet2!BI2:Sheet2!D3190,0)) will return false if the result is not a number. In your case, all your postal codes (I assume) have at least 1 letter. This won't work for you.

         

        Try my original formula 

        =NOT(ISERROR(MATCH(F2,Sheet2!A:A,0)))

        and ensure all your postal codes are listed in a single column A of sheet 2. If they are in a different column, change "A:A" in the formula to match the column you're using. (e.g. B:B or C:C ...)

         

         

Resources