Excel Formulas

[gsn9877]

Here's the scenario... I have a spreadsheet that detials the different zones
in our network. Unfortunately the zones are identified as letters and
numbers. Each time I receive the report, I have to look up the Zone ID's and
manually enter their corresponding names in the spreadsheet. There are about
100 zones, and I'd like to use an "IF" formula or something that I could use
to automatically convert the ID to the correct corresponding name.

Any suggestions would be helpful.

 

[Dennis]

Check the VLOOKUP worksheet function in the help.

 

[Pete_UK]

You would normally use a VLOOKUP function for this. Suppose your table
is in M2:N100, with the ZoneID in column M and the name in column N.
Put a zone ID in A1 and this formula in B1:

=VLOOKUP(A1,M$2:N$100,2,0)

and it will give you the corresponding name. Change the ID in A1 to
get a different name automatically.

Hope this helps.

Pete

 

[gsn9877]

I'm not very good with formulas, so I'm still not getting it. I have a
spreadsheet that in column B, there are just numbers (zone ID's). These are
8 digit numbers, and there are a total of 50 different numbers. In column C
I'd like it to look up the number and return the actual zone name. I tried
this formula, but it didn't work:

=LOOKUP(B9,{12406045,13986375,21594687,21595296,21595522,21625160,21625161,21625162,21625163,21625164,21625165,21625166,21625168,21625169,21625170,21625171,21625172,21625195,21625196,21625198,21625200,21625202,21625204,21625211,21625212,21625240,21625243,23118928,23586194,23709425,23810713,23903776,24076507,24076545,24116539,24129383,24159350,24247943,24512471,24601565,24787332,24847119,24867068,24910067,24914061,24929310,25094600,25127195,25279727,25653758,25678057,25700715,25747427},{“Defaultâ€,“Defaultâ€,“Village
Marketâ€,“Remke Marketsâ€,“Met Foodsâ€,“Buehlers Buy
Lowâ€,“Cashwiseâ€,“Cobornsâ€,“Country Martâ€,“Dicks Fresh Marketâ€,“Fairway
Marketâ€,“Festival Foodsâ€,“Giant Carlisleâ€,“Jerrys Foodsâ€,“Kingsâ€,“Lucky
Supermarketsâ€,“Martinsâ€,“Smith Brothers IGAâ€,“Niemanns County
Marketâ€,“Niemanns Cub Foodsâ€,“Northern Food Kingâ€,“Pioneerâ€,“Porricellis Food
Martâ€,“Strack and Van Tillâ€,“Savemartâ€,“The Fresh
Grocerâ€,“Ukropsâ€,“Plumbsâ€,“Eldens Food Fairâ€,“Gorettisâ€,“Grand
Unionâ€,“Holiday Market of Cantonâ€,“Garys Foodsâ€,“Foodlandâ€,“Forest Hills
Foodsâ€,“Kaune Foodtownâ€,“Rogers Foodlandâ€,“Hunters IGAâ€,“Beit
Brothersâ€,“SuperValu Paducahâ€,“Ogles Foodsâ€,“Mackenthuns County Marketâ€,“Bobs
Produceâ€,“Better Valu Supermarketsâ€,“Dillonvale IGAâ€,“Ruggieris
Marketâ€,“Hudson County Marketâ€,“Sullys Superetteâ€,“Tri Town Foodsâ€,“West Side
Marketplaceâ€,“Soderquists Marketâ€,“Teds IGAâ€,“Pats Food Centerâ€})


What am I missing here.... It keeps saying I have an error in my formula?

 

[Gord Dibben]

Assuming 50 numbers are in column B and 50 zone names are in Column C

Try this entered in D1

=VLOOKUP(A1,$B$1:$C$50,2,FALSE)

Enter a number in A1.


Gord Dibben MS Excel MVP