This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I'm using Office Professional Plus 2019.
I have created a formula to search an existing reference table and return a result, however it is not working as it should and I can't understand it's logic!
The formula is =IFERROR(VLOOKUP(A2, $J$1:$K$131, 2,FALSE), "Brisbane") [If A2 is the column with the suburb listed, and J1:K131 is the reference table)
The goal of the formula is to search is the suburb is present in the table, if so to return the region listed next to the suburb, and if it is not listed then it should return "Brisbane".
Here is a sample of the reference table:
| Suburb | Region |
|---|---|
| Allenview | Logan Beaudesert |
| Bahrs Scrub | Logan Beaudesert |
| Banksia Beach | Moreton Bay North |
| Bannockburn | Logan Beaudesert |
| Barney View | Logan Beaudesert |
| Beachmere | Moreton Bay North |
| Beaudesert | Logan Beaudesert |
| Beenleigh | Logan Beaudesert |
| Belivah | Logan Beaudesert |
| Bellara | Moreton Bay North |
| Bellmere | Moreton Bay North |
| Bellthorpe | Moreton Bay North |
| Berrinba | Logan Beaudesert |
| Bethania | Logan Beaudesert |
| Biddaddaba | Logan Beaudesert |
| Bongaree | Moreton Bay North |
| Boronia Heights | Logan Beaudesert |
| Browns Plains | Logan Beaudesert |
| Buccan | Logan Beaudesert |
Here is a sample of the actual suburb data, and the results that are being returned:
| Suburb | Region |
|---|---|
| Albany Creek | Brisbane - Right |
| Alexandra Hills | Brisbane - Right |
| Anstead | Logan Beaudesert (Wrong - should be Brisbane) |
| Arana Hills | Brisbane - Right |
| Aspley | Brisbane - Right |
| Banyo | Logan Beaudesert (Wrong - should be Brisbane) |
| Bardon | Brisbane - Right |
| Beaudesert | Moreton Bay North - (Wrong - should be Logan Beaudesert) |
| Beenleigh | Logan Beaudesert - Right |
| Bellbird Park | Moreton Bay North - (Wrong - should be Brisbane) |
| Bellmere | Moreton Bay North - Right |
| Bethania | Logan Beaudesert - Right |
| Birkdale | Logan Beaudesert - (Wrong - should be Brisbane) |
| Boronia Heights | Moreton Bay North - (Wrong - should be Logan Beaudesert) |
| Bracken Ridge | Logan Beaudesert - (Wrong - should be Brisbane) |
| Brassall | Logan Beaudesert - (Wrong - should be Brisbane) |
| Brighton | Logan Beaudesert - (Wrong - should be Brisbane) |
| Brookfield | Logan Beaudesert - (Wrong - should be Brisbane) |
| Browns Plains | Logan Beaudesert - Right |
I don't understand the logic and where the formula has gone wrong. Can you please help me write a new formula or fix my existing one?
The logic I need it to return is essentially: IF Suburb listed against Region A OR Region B, then list region name (Logan Beaudesert or Moreton Bay North), if not list Brisbane as region.
Thanks in advance!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Could you share us a test workbook to reproduce your issue?
The formula should work. You may test copying the cell from column A to column J.
Answer accepted by question author
Check your reference table. In the copy in your first post, there is a space after each of the Suburb names: "Allenview " etc.
If those spaces are present in your workbook, you should remove them.
Answer accepted by question author
I think I found the problem! There was a space hiding at the end of each cell in the reference table!!!! I'm so annoyed. I have spent hours working on this.
Thanks so much for your help!!!
Sorry that was a typo now amended. I'm still having the same issue with the formula.
To clarify, now that I've changed it to "false" instead of returning random results, it returns only Brisbane. So I'm not sure what I've done wrong with this formula! Can you help me write a new one?
You want exact matches, so the formula should be
=IFERROR(VLOOKUP(A2, $J$1:$K$131, 2, FALSE), "Brisbane")