1=City, 2=Roskill

[Guest]

Hello from Steved

I would like to please use only the first number as a identifier.

for example 1234, 1543, 1789, 1673 in this case the 1 will = City
for example 2234, 2543, 2789, 2673 in this case the 2 will = Roskill

Thankyou.

 

[Guest]

Sorry I posted twice by mistake.

 

[Guest]

=IF(LEFT(A1)="1","City",IF(LEFT(A1)="2","Roskill",""))

Regards
Rowan

 

[Max]

Pl see one response in your later post ..

 

[Guest]

Helllo Rowan from Steved

Question please
=IF(LEFT(F6)="1","City",IF(LEFT(F6)="2","Roskill",IF(LEFT(F6)="3","Papakura",IF(LEFT(F6)="4","Wiri",IF(LEFT(F6)="5","Shore",IF(LEFT(F6)="6","Orewa",IF(LEFT(F6)="7","Swanson","")))))))

I would like to add 2 more in ie
IF(LEFT(F6)="8","Panmure",IF(LEFT(F6)="9","Waiheke",

With your formula you kindly gave me is it possible.

Thankyou.

 

[Max]

Steved,

Posted the response below in your other thread ..
For easier maintenance, and to avoid the IF nesting limits,
you might to want to try the VLOOKUP way ..
---
One way ..

Assuming you have set-up the reference table
in Sheet1, cols A & B, from row1 down as:

1 City
2 Roskill
etc

Then in another sheet, say, Sheet2, if the numbers:
1234, 1543, 1674, 2234, 2543, 2674 etc are in A1 down,
we could put in say, B1:

=IF(A1="","",IF(ISNA(MATCH(LEFT(TRIM(A1))+0,Sheet1!A:A,0)),"No
match",VLOOKUP(LEFT(TRIM(A1))+0,Sheet1!A:B,2,0)))

and copy B1 down

Col B will return the desired results

Unmatched cases, if any, will return the phrase: No match

 

[Guest]

Steved

You can only have 7 nested ifs. To get more your best bet would probably be
to use a Vlookup with a refrence table. Set the table up like this
1 City
2 Roskill
etc

My reference table is in range I1 to J9 so then the vlookup would be
something like:
=VLOOKUP(--LEFT(A1),$I$1:$J$9,2,0)

See http://www.contextures.com/xlFunctions02.html for more details on Vlookups

Regards
Rowan

 

[Guest]

Hello Rowan From Steved

Please ignore my ealier post to you as it has been answered
Thanks very much for your time on my issue.

 

[Guest]

Hello Max yes I posted it twice by mistake as Microsoft site must have went
off line
hence I thought I had not sent the first one.

Cheers.