Substitue for the IF Function

[KimC]

I'm needing to display the correct area code for the corresponding postcode,
row two is the example i'm needing to display. The postcode is already there,
it the area code i need the formula for.

A B
1 Postcode AreaCode
2 2148 02

The data is
1XXX & 2XXX = 02
3XXX, 7XXX & 8XXX = 03
4XXX & 9XXX = 07
0XXX, 5XXX & 6XXX = 08

The "X" refer to different numbers that may be entered.

Thanks in advance

 

[RagDyer]

Say the data is in D1, then:

=Right(D1,2)

 

[mohavv]

I'm needing to display the correct area code for the corresponding postcode,
row two is the example i'm needing to display. The postcode is already there,
it the area code i need the formula for.

           A               B
1    Postcode    AreaCode
2       2148           02

The data is
1XXX & 2XXX = 02
3XXX, 7XXX & 8XXX = 03
4XXX & 9XXX = 07
0XXX, 5XXX & 6XXX = 08

The "X" refer to different numbers that may be entered.

Thanks in advance

Hi, Best way to do this, and keep it flexible, is via a table and
VLOOKUP

Create following table and name it "code". This can be on another
sheet, preferably.

PC AC
0 08
1 02
1 02
3 03
4 07
5 08
6 08
7 03
8 03
9 07

use following formula in B2: =VLOOKUP(VALUE(LEFT(A2,1)),code,2,0)

This should work.

Cheers,

Harold

 

[Tyro]

Assuming your data in A2 is text you can put this in B2:
=CHOOSE(LEFT(A2,1)+1,"08","02","02","03","07","08","08","03","03","07","08")

Tyro

 

[mohavv]

Say the data is in D1, then:

=Right(D1,2)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !









- Show quoted text -

Best way to do this is using a table and VLOOKUP

Create following table and name it "code". Preferably on another
sheet.

PC AC
0 08
1 02
2 02
3 03
4 07
5 08
6 08
7 03
8 03
9 07

use following formula in B2: =VLOOKUP(VALUE(LEFT(A2,1)),code,2,0)

Best of luck.

Cheers,

Harold

 

[JLatham]

KimC, if I understand correctly, any 4-digit post code beginning with 1 has
area code 02, any 4-digit post code beginning with 0, 5 or 6 has area code of
08.

If this is correct, you can set up a small table on that sheet or another
one like this:
A B
1 0 8
2 1 2
3 2 2
4 3 3
5 4 7
6 5 8
7 6 8
8 7 3
9 8 3
0 9 7

Assuming that table is on a sheet named Sheet1 it can be referred to as:
'Sheet1'!$A$1:$B$10
in a formula.

So in your example you have cell A2 containing 2148
In B2 you'd put
=VLOOKUP(INT(A2/1000),Sheet1!$A$1:$B$10,2,0)
the INT(A2/1000) reduces the 2148 to just "2" for the lookup. It will
return a 2 from the table and you can format the cell to display a leading
zero by formatting the column (B) as custom and entering 00 as the format.

The same formula will work all down the sheet as you 'fill' it (see help on
Fill Data) on how to do that. The only part of it that will change is the A2
reference. Lets say you have 6115 in A7, then the formula in B7 would be:
=VLOOKUP(INT(A7/1000),Sheet1!$A$1:$B$10,2,0)
and it would return an 8 from the table.

Hope this helps some.

 

[Tyro]

Correction formula should be
=CHOOSE(LEFT(A1,1)+1,"08","02","02","03","07","08","08","03","03","07")