Finding text from another list

[Groves22]

This may be easy... or not, but I can't figure out what to do.

I'm trying to format a list of addresses. They were given to me in 1 column, however, I had to break them out (using 'Text to Columns') each word to it's own column. So now the address ranges from 3 to 7 columns long, depending how many words were in the address.

(ie. old: [123 Big Walk Way], new: [123][Big][Walk][Way], [] symbolizes a column)

I have a list of street suffixes from USPS's website... What I need to do, is scour all columns, and try to pull out the suffix and place in 1 column.

Another detail, some entries will have Street, while others may have St. I need to have everything abbreviated, which I have in the same table as the suffixes. (ie. columnA: Street, columnB: St.)

I hope that made sense...

What I want to happen: (Address :: Suffix column)
[12][Big][Leaf][Avenue] :: [Ave]
[123][3][Mile][Journey][St] :: [St]

 

[Groves22]

=IF(ISERROR(VLOOKUP(getlastword($D2,F$1),'street suffix'!$A$4:$B$1011,2,FALSE)),"",VLOOKUP(getlastword($D2,F$1),'street suffix'!$A$4:$B$1011,2,FALSE))

It's the same formula from the other tab... everything is identical, that's why I don't understand why it's not working.

When it gets to getlastword($D2,F$1), it returns "", which errors out the vlookup.

 

[Groves22]

Ah shoot...

I just noticed I didn't TRIM the address. That's why it's returning a blank. The address looks like "123 Walk This Way ".

Problem solved! Thanks for looking into it, though!