|
Excel Formulas
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. -- Learning something new is an adventure we can all participate in! |
Excel Formulas
Check the VLOOKUP worksheet function in the help.
"gsn9877" wrote: 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. -- Learning something new is an adventure we can all participate in! |
Excel Formulas
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 On Mar 31, 3:58*pm, gsn9877 wrote: 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. -- Learning something new is an adventure we can all participate in! |
Excel Formulas
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,21 595522,21625160,21625161,21625162,21625163,2162516 4,21625165,21625166,21625168,21625169,21625170,216 25171,21625172,21625195,21625196,21625198,21625200 ,21625202,21625204,21625211,21625212,21625240,2162 5243,23118928,23586194,23709425,23810713,23903776, 24076507,24076545,24116539,24129383,24159350,24247 943,24512471,24601565,24787332,24847119,24867068,2 4910067,24914061,24929310,25094600,25127195,252797 27,25653758,25678057,25700715,25747427},{€śDefaul t€ť,€ś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? -- Learning something new is an adventure we can all participate in! "Pete_UK" wrote: 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 On Mar 31, 3:58 pm, gsn9877 wrote: 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. -- Learning something new is an adventure we can all participate in! |
Excel Formulas
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 On Tue, 1 Apr 2008 11:22:03 -0700, gsn9877 wrote: 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,2 1595522,21625160,21625161,21625162,21625163,216251 64,21625165,21625166,21625168,21625169,21625170,21 625171,21625172,21625195,21625196,21625198,2162520 0,21625202,21625204,21625211,21625212,21625240,216 25243,23118928,23586194,23709425,23810713,23903776 ,24076507,24076545,24116539,24129383,24159350,2424 7943,24512471,24601565,24787332,24847119,24867068, 24910067,24914061,24929310,25094600,25127195,25279 727,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? |
| All times are GMT +1. The time now is 12:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com