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 "D...
excel
Formulas and Functions
Bennadeau's avatar
Bennadeau
Iron Contributor
Aug 28, 2020

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
    Sep 01, 2020

    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
      Sep 01, 2020

      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 ...)

       

       

      • burrdam's avatar
        burrdam
        Copper Contributor
        Jan 13, 2022
        For anyone using this article for reference, there is nothing wrong with the isnumber() version of the formula - this will simply return TRUE if there's a valid match (MATCH returns the match by its numerical position - ISNUMBER converts it to boolean values).

        The issue I can see with Jolange's formula is the missing comma between 'F2' and 'Sheet2!....'
        =ISNUMBER(MATCH(F2Sheet2!BI2:Sheet2!D3190,0)). This would cause the MATCH formula to error, which is not a number, so would return FALSE as the final value in every case.

        Hope this helps

Resources