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