Discussion Re: checking postcode is on a list formula in Excel
https://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1625955#M73717
<P>Hi <LI-USER uid="774670"></LI-USER>,</P><P> </P><P>This formula: <SPAN>=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.</SPAN></P><P> </P><P><SPAN>Try my original formula </SPAN></P><P><SPAN>=NOT(ISERROR(MATCH(F2,Sheet2!A:A,0)))</SPAN></P><P><SPAN>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 ...)</SPAN></P><P> </P><P> </P>Tue, 01 Sep 2020 18:28:11 GMTBennadeau2020-09-01T18:28:11Zchecking postcode is on a list formula
https://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1618925#M73415
<P>I am trying to get a formula that will check an entry in column E is on a list and answer yes or no.</P><P>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.</P>Fri, 28 Aug 2020 14:28:16 GMThttps://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1618925#M73415Jolange2020-08-28T14:28:16ZRe: checking postcode is on a list formula
https://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1618991#M73418
<P>Hi <LI-USER uid="774670"></LI-USER>,</P><P> </P><P>You can use this formula...</P><P>=NOT(ISERROR(MATCH(A1,Sheet2!A:A,0)))</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Bennadeau_0-1598625509917.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/215243i27CE42297D61B440/image-size/medium?v=v2&px=400" role="button" title="Bennadeau_0-1598625509917.png" alt="Bennadeau_0-1598625509917.png" /></span></P><P>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".</P>Fri, 28 Aug 2020 14:41:29 GMThttps://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1618991#M73418Bennadeau2020-08-28T14:41:29ZRe: checking postcode is on a list formula
https://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1620558#M73469
<P><LI-USER uid="774670"></LI-USER> As a variant:</P><LI-CODE lang="excel">=ISNUMBER(MATCH(A1,Data1!A:A,0))</LI-CODE><P> </P>Sat, 29 Aug 2020 09:01:17 GMThttps://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1620558#M73469Riny_van_Eekelen2020-08-29T09:01:17ZRe: checking postcode is on a list formula
https://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1624493#M73668
<P>Hi</P><P><LI-USER uid="403176"></LI-USER> 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.</P>Tue, 01 Sep 2020 07:03:30 GMThttps://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1624493#M73668Jolange2020-09-01T07:03:30ZRe: checking postcode is on a list formula
https://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1624498#M73669
<P><LI-USER uid="703724"></LI-USER> 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</P><P>=ISNUMBER(MATCH(F2Sheet2!BI2:Sheet2!D3190,0))</P><P>I was using YO126EY as a test and this is exactly how it is in the list i was given.</P>Tue, 01 Sep 2020 07:10:00 GMThttps://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1624498#M73669Jolange2020-09-01T07:10:00ZRe: checking postcode is on a list formula
https://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1624581#M73671
<P><LI-USER uid="774670"></LI-USER> 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?</P><P> </P><P> </P>Tue, 01 Sep 2020 07:53:29 GMThttps://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1624581#M73671Riny_van_Eekelen2020-09-01T07:53:29ZRe: checking postcode is on a list formula
https://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1625955#M73717
<P>Hi <LI-USER uid="774670"></LI-USER>,</P><P> </P><P>This formula: <SPAN>=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.</SPAN></P><P> </P><P><SPAN>Try my original formula </SPAN></P><P><SPAN>=NOT(ISERROR(MATCH(F2,Sheet2!A:A,0)))</SPAN></P><P><SPAN>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 ...)</SPAN></P><P> </P><P> </P>Tue, 01 Sep 2020 18:28:11 GMThttps://techcommunity.microsoft.com/t5/excel/checking-postcode-is-on-a-list-formula/m-p/1625955#M73717Bennadeau2020-09-01T18:28:11Z