Search : in
By :

Excel Formula/Macro Help

Last answer on Sep 28, 2009 6:05:05 am BST Cat, on Sep 26, 2009 3:48:33 am BST 
 Report this message to moderators

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

Configuration: Windows XP Internet Explorer 7.0

Best answers for « Excel Formula/Macro Help » in :
Excel formula to get the cell with the text Show
Excel – Formula for cell calculation Show Excel – Formula for cell calculation Issue Solution Note Issue Simple formula of =J3-SUM(L3:X3) BUT if J3 is empty then I want the formula to run as =C3-SUM(L3:X3) What is the proper formula to get the calculation to utilize J3 if...
Excel Formula Display in Cell (Solved) Show
[Excel] – Running Macro Automatically Show[Excel] – Running Macro Automatically Issue Solution Issue I have a macro in excel which needs to be run twice a day and I don’t even want to open the excel sheet. How can I make this process automatic? If I can convert the macro...
[Excel]changing cell formula to text Show[Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Special characters ShowSpecial character codings HTML standards require all code to be written in 7-bit ASCII, which means that accented characters are not allowed. Despite this, current browsers recognise accented characters; you can enter accented characters directly...
Ask your question Reply

1

venkat1926, on Sep 27, 2009 1:57:30 am BST

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.

   
Reply to venkat1926

2

cat, on Sep 28, 2009 5:46:52 am BST

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...

   
Reply to cat

3

cat, on Sep 28, 2009 5:46:52 am BST

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...

   
Reply to cat

4

 venkat1926, on Sep 28, 2009 6:05:05 am BST

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)

   
Reply to venkat1926
Ask your question Reply
They need your help