Excel Formula/Macro Help
Closed
Cat
-
Sep 25, 2009 at 10:48 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 28, 2009 at 01:05 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 28, 2009 at 01:05 AM
Related:
- Excel Formula/Macro Help
- Excel grade formula - Guide
- Number to words in excel formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Credit summation formula - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 26, 2009 at 08:57 PM
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.
=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.
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...
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...
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...
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...
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 28, 2009 at 01:05 AM
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)
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)
