I have a spreadsheet that looks up a area code from the postcode. I am using vlookup but it is giving incorrect answers. it works unless the postcode contains only 1 letter in the first set of characters. ie ne26 works bu n1 doesnt. is there a alternative to lookup?
I have attahced the spreadsheet.
Last edited by toclare84; 08-31-2010 at 07:34 AM.
I can't download 2007 macro-enabled spreadsheets... but I think I could help if you could save it as an xlsx.
CC
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
Hi,
I have changed the format for you. thanks for your help!
What result were you expecting for N1? It doesn't appear to be on your postcodes sheet.
there is no N1, N2, N3 as all the postcodes begining with N should have the code G. see i can get it to find an exact match, but i then need for the formula to march to the letter if there is no exact match. does that make sense?
so lets say there is a N33 in my list and a N.
when i enter N33 it will find it as it is an exact match.
but if i enter N20 (as it doesnt appear in my list it should find the N match and bring back that result.
Hi,
I have a postcode finder that matches the postcode to an area. it works for exact matches, but i need to add to this code:
=VLOOKUP(LEFT(B3,IF(ISNUMBER(MATCH(LEFT(B3,4),POSTCODES!A:A,0)),4,IF(ISNUMBER(MATCH(LEFT(B3,3),POSTCODES!A:A,0)),3,2))),POSTCODES!A:B,2,0)
to say that if there is not an exact match, then to match B6 to the postcodes instead. so the formula would run again but instead of using B3 it would use B6.
If I've understood then given use of XL2007
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Why have you started a new thread on this subject?
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
thats great it works brilliantly!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks