Excel Formula/Macro Help

Closed
Cat - Sep 25, 2009 at 10:48 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 28, 2009 at 01:05 AM
Hello,
I need to be able to look up a postcode and find what zone it is in. The problem is I only have start and end postcodes for the zones and not each of the postcodes in between. Sample data below. So, if I input postcode 1725 on my data entry sheet I need it to know the zone is "SYD" and then it can find the relevant rate for that zone in another table (I can do that bit ok!)


Start End Zone
0800 0821 DWN
0822 0822 NT2
0828 0836 DWN
0837 0837 NT1
0840 0840 DWN
1701 1797 SYD
1798 1799 NS1

4 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 26, 2009 at 08:57 PM
try this formula

=VLOOKUP(A15,$A$1:$C$8,3,1)

if the relevant no. is 1725 teh result of the formula will be SYD

the argument 3 means that the operative column is A(first column -start")
the last argument 1 it finds(not necessarily the exact value) in the column A1 to C8 the value immediately less than the number you specified.(in this case it find 1701)

study vlookup under help . it is very useful function.
0
Hi,
Thank you for your response. I use vlookup all the time but was having a bit of a 'blonde moment' with this one! It does work ok for the most part, but I had it in my mind that I wanted something to check both the start postcode & end postcode so that it would pick up if one was not listed (there are gaps in the postcode sequencing - see below 0883 & 0884 are not listed) and then indicate it was not valid.

0882 0882 GOV
0885 0885 NT2
0886 0899 NT1
0900 0910 ASP


I am still interested to see if someone has a macro that will automatically list downward all the postcodes with each range. So for the example above like this:
0882 GOV
0885 NT2
0886 NT1
0887 NT1
0888 NT1
0889 NT1
0900 ASP
0901 ASP
0902 ASP
etc...
0
Hi,
Thank you for your response. I use vlookup all the time but was having a bit of a 'blonde moment' with this one! It does work ok for the most part, but I had it in my mind that I wanted something to check both the start postcode & end postcode so that it would pick up if one was not listed (there are gaps in the postcode sequencing - see below 0883 & 0884 are not listed) and then indicate it was not valid.

0882 0882 GOV
0885 0885 NT2
0886 0899 NT1
0900 0910 ASP


I am still interested to see if someone has a macro that will automatically list downward all the postcodes with each range. So for the example above like this:
0882 GOV
0885 NT2
0886 NT1
0887 NT1
0888 NT1
0889 NT1
0900 ASP
0901 ASP
0902 ASP
etc...
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 28, 2009 at 01:05 AM
I am little confused. if your date is like this

0882 GOV
0885 NT2
0886 NT1
0887 NT1
0888 NT1
0889 NT1
0900 ASP
0901 ASP
0902 ASP

then you use the last argument s 0 insted of 1 because you have exact value. and the theird argument will be 2 insted of 3.
=VLOOKUP(A15,$A$1:$C$8,2,0)
0