Huge replacement formula

**Falcon Punch** · 11-30-2011, 05:11 PM

I'm running an online store, and my dropshipper gives me our product list in a format where most of the needed information is found in the item number.

Example: 1AALIPGSPH07800
Category: 1A
Manufacturer Code: ALI
Etc.

I easily built a formula to isolate the categoryID and manufacturer ID into their own columns. I then used a formula to replace the categoryID into the format needed for the online store,

Example: =IF(AE2="1A","Skate/Essentials/Hardware","")&IF(AE2="1B","Skate/Essentials/Bearings","")& etc etc.

There's only about 40 categories, so no issue there.

The issue I'm having is applying the same method to replace the manufacturer ID with the full manufacturer name. There's over 650 manufacturers, which puts that formula at about 17k characters, well over Excel's limits. If the supplied item list didn't change constantly, I would just do this manually, but with 13k items changing every other week, that's just not an option. Any idea of another way to do this?

**Special-K** · 11-30-2011, 06:00 PM

Could you use a VLOOKUP table?

**Falcon Punch** · 11-30-2011, 06:50 PM

Not sure, that's why I'm asking.

If that were to work, how would I go about it?

**daddylonglegs** · 11-30-2011, 07:01 PM

You can create a 2 column table. For the category IDs for example just list all the IDs in one column, e.g. in Y2 down, and in the next column, e.g. Z2 down, you list the description ID. Now for your formula use just

=VLOOKUP(AE2,$Y$2:$Y$50,2,0)

You can do the same thing for your manufacturer IDs

**Haseeb Avarakkan** · 11-30-2011, 07:36 PM

Originally Posted by daddylonglegs

=VLOOKUP(AE2,$Y$2:$Y$50,2,0)

A minor change, it should be $Z$50 instead of $Y$50

=VLOOKUP(AE2,$Y$2:$Z$50,2,0)

**daddylonglegs** · 12-01-2011, 08:51 AM

Exactly right! my mistake, thanks Haseeb

**Falcon Punch** · 12-01-2011, 11:12 AM

So I set up another worksheet ("Brands") with the first column full of the Manufacturer ID's and the second column full of the matching Names. Here's the formula I'm using on the Master sheet:

=VLOOKUP(Master!B2,Brands!A2:B651,Brands!B2:B651,FALSE)

This method definitely seems like it will be easier, but the problem is now I'm geting a #REF! error.

Thanks for the help so far, hopefully we can get this figured out.

Edit: Got it. Formula used: =VLOOKUP(Master!B2,Brands!A2:B651,2,0)

Double Edit: I lied. It's showing values for the first few hundred, but the rest return an #N/A response.

**Haseeb Avarakkan** · 12-01-2011, 06:10 PM

Originally Posted by daddylonglegs

Exactly right! my mistake, thanks Haseeb

You are welcome DDL

Originally Posted by Falcon Punch

Formula used: =VLOOKUP(Master!B2,Brands!A2:B651,2,0)

It's showing values for the first few hundred, but the rest return an #N/A

If you are copying down, you need to lock the table_array range or use whole column reference.

=VLOOKUP(B2,Brands!A$2:B$651,2,0)

Or, whole columns reference

=VLOOKUP(B2,Brands!A:B,2,0)