I work with addresses, i have a column named ADDRESS and another column SUBURB,and a list of all the suburbs in another sheet. But the data i get the street address and the Suburb are in the one column under ADDRESS.So I want to do a vlookup looking through the ADDRESS column for suburbs in my SUBURB list. But i cant do it because i cant use exact match as the street address is in the same columns.

Why don't you separate the address from the suburb and put them in 2 different columns? You didn't give us any details as to how your data looks so we can't offer too much in the way of suggestions to help you accomplish that.

here is a example i have all these in my address column: 3 Oxford Crt Southside 4 Garnet Crt Southside 17 Crown Crt Stephens 19 Sonama Crt Stephens 21 Crown Crt Stephens 21 Crown Crt Stephens 25 Washington Crt Stephens 7 Rina Crt Stephens 7 Rina Crt Stephens Unit 3 7 Flint Crt Stephens 810 Figwood Crt Stockleigh 15 Philip Crt Summerholm Now most of the Suburbs is after the address abreviation, (e.g Crt) is there a way i can a vlookup comparing the address to my suburb master list so it picks up the Suburb in the address??

Piece of cake... With your address data in A1:A12, your VLOOKUP table_array in C1:C12, put this in B1 and drag it down. It will VLOOKUP the suburb in the 1st column of the table_array and pull data from the 2nd. =VLOOKUP(MID(SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,256),$C$1:$D$12,2,0) The MID() formula buried in there will pull the out last word in a text string - the suburb - assuming the string doesn't have any trailing spaces. No, I didn't write it...Google is my friend.

Will this still work if the suburb is not the last word in the street address??

Uh...did you test it to see for yourself? When you're done testing it, look here: http://www.ozgrid.com/Excel/extract... P.S. Feel free to drop a "thanks" in here someplace, especially before you ask for more help.