Can I Find/Replace multiple items?

Posted by Jeremy on December 18, 2001 12:38 PM

I have a long list of formulas that use ID #s. See example:

NT.1999 = 7886 + 8776

I want to convert the ID numbers to the text that the ID number represents. The formula above should read:

SW Region = Portland + Seattle

I have a file that defines all the Regions with two columns:
Example: NT.1999, Total Region

I have another file that defines all the markets:
7886, Portland
8776, Seattle

Is there any way to access these other lists? I know I could just do a replace all for each ID number, but with lists over 100 items this would take FOREVER.

Posted by Tracy Mangano on December 18, 2001 12:58 PM

Use VLOOKUP

=VLOOKUP(NT.1999 cellref, range_that_defines_the_regions,2,false)&"="& VLOOKUP(7886 cellref, range_that_defines_the_markets,2,false)&"+"& VLOOKUP(8776 cellref, range_that_defines_the_markets,2,false)

Posted by Jeremy on December 18, 2001 1:25 PM

It is not always a consistent formula. Sometimes 3,4,5 ID numbers make up a definition. Can you make it dynamic?

Posted by Tracy Mangano on December 18, 2001 1:54 PM

I have sent you an e-mail with a spreadsheet attached showing how to do what you have asked.