checking postcode is on a list formula

[Jolange]

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.

[#2]

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

[#3]

@Jolange As a variant:

=ISNUMBER(MATCH(A1,Data1!A:A,0))

[#4]

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.

[#5]

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

[#6]

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

[#7]

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

[#8]

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