Address sorting formula

[joesbox]

Good day all,

New to the forum but here goes. I am using Excel 2013. I receive a list of addresses on a daily basis. but the address I receive is only the number of the house. The number determines the community name.

example: address 52303-1 belongs in Comanche III community. the "-1" means that the address is apartment 1 of 2.

Excel stores the address as text because of the -1.

I have created a monster of a nested if statement to cover all of the communities and the range of addresses. I am sure that I am close but I think that instead of looking at the numbers as alpha characters I am trying to look at them as numeric characters. here is the formula I have created:

=IF(C5="","",
IF(AND(C5>=100,C5<1000),"MCNAIR",
IF(AND(C5>=48302,C5<48900),"KOUMA",
IF(AND(C5>=5200,C5<5700),"PERSHING",
IF(AND(C5>=6000,C5<6600),"CHAFFEE",
IF(AND(C5>=8100,C5<8700),"WALKER",
IF(AND(C5>=51000,C5<52000),"COM 1 & 2",
IF(AND(C5>=52000,C5<54000),"COM III",
IF(AND(C5>=60000,C5<70000),"VENABLE",
IF(AND(C5>=70000,C5<84400),"MONTAGUE",
IF(AND(C5>=5700,C5<6000),"WAINWRRIGHT",
IF(OR(AND(C5>=6600,C5<6900),AND(C5>=7100,C5<7300)),"PATTON",
IF(AND(C5>=48000,C5<48301),"LIBERTY","ERROR")))))))))))))

That is from "A5". Below is a picture of the excel doc if it helps. as you can see, in "A2" there is no -# with the address. this is the only community that has single family homes.

<strike></strike>

 

[MarcelBeug]

Instead of using nested IF-statements, use a table with the lower boundaries in the first column and the Neighborhood in the second. Include Neighborhood ERROR for any gaps.
Make sure the first column is sorted in ascending order.
If you name the list "CommunityTable", you can use formula:

=VLOOKUP(--LEFT(C2,FIND("-",C2&"-")-1),CommunityTable,2)

 

[joesbox]

Instead of using nested IF-statements, use a table with the lower boundaries in the first column and the Neighborhood in the second. Include Neighborhood ERROR for any gaps.
Make sure the first column is sorted in ascending order.
If you name the list "CommunityTable", you can use formula:

=VLOOKUP(--LEFT(C2,FIND("-",C2&"-")-1),CommunityTable,2)

I appreciate that. This works exactly as I need it to. Thank you so much.

 

[MarcelBeug]

You're welcome.