Forum Discussion
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
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.
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?
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".
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.
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 ...)
