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 am returning an N/A error in a VLookup formula that is referencing the value returned by a =right formula and I cant fix it.
Firstly I have a formula to extract the last 4 digits of a field, that is a post code ie 3300... that seem to work fine
I then have a vlookup formula to lookup the value returned by that = right formula ie 3300 and then return the name of the corresponding Suburb from a seperate TAB
I get an N/A error.
If I manually override the value returned by the = right formula, then the vlookup works...????
Any idea how to fix this???????
Example below
| Address Column C | Postcode Column D | Suburb |
|---|---|---|
| 1 McKay St , Learmonth VIC 3352 | =RIGHT(C2,4) | =VLOOKUP(D2, Postcode!A:B, 2, FALSE) |
| Address | Postcode | Suburb |
|---|---|---|
| 1 McKay St , Learmonth VIC 3352 | 3352 | #N/A |
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.
![](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(64, 92%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERM%3C/text%3E%3C/svg%3E)
The RIGHT function returns a text value even if it looks like numbers. Make this modification to your RIGHT function which will convert the numbers stored as text to number values. Excel will then be able to match that number value to the number value in your Lookup Table.
=--RIGHT(C2,4)
Thanks
Thats another good option
Hi Iain
The above solution is 100% correct,
But also, for the Postcode column, you may try the formula =VALUE(RIGHT(C2,4))
I hope this helps you
Regards
Jeovany
The RIGHT function returns a text value even if it looks like numbers. Make this modification to your RIGHT function which will convert the numbers stored as text to number values. Excel will then be able to match that number value to the number value in your Lookup Table.
=--RIGHT(C2,4)
Thanks Rich M... That worked a treat..!!!