The VLOOKUP function can do this. For example suppose you have your
abbreviations and their corresponding text values in cells C1:D20.
Then, you can use

=VLOOKUP(A1,C1:D20,2,FALSE)

This will look in C1:C20 for the value in A1 and, if found, returns
the corresponding value in D1:D20. If the abbreviation in A1 is not
found in C1:C20, the function will return an #N/A error. If you want
the formula to return an empty string (or something else) if the
abbreviation is not found, use something like

=IF(ISERROR(VLOOKUP(A1,C1:D20,2,FALSE)),"",VLOOKUP (A1,C1:D20,2,FALSE))

This is not terribly efficient because the VLOOKUP must be calculated
twice in most circumstances, first to test the result for an error and
again to get the value if it is not an error.

In Excel 2007, you can use the IFERROR function to cause the VLOOKUP
to be called only once:

=IFERROR( VLOOKUP(A1,C1:D20,2,FALSE),"")

IFERROR is new in Excel 2007 and cannot be used in Excel 2003 and
earlier versions.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 20 May 2009 11:45:39 -0700 (PDT), wrote:

How can I match abbreviations in a cell to a list of words and return
their values if the text is various word lengths and I need to match
each word? Thanks.