TTQ B4U Posted December 16, 2015 Report Share Posted December 16, 2015 I know excel pretty well but not a power user for fancy formulas. This one is easy but I'm dumb. Please help me understand how to do this. Situation: I have a spreadsheet with thousands of accounts and about 1,400 zip codes. When moving territories around based on zip code, the account executive code for them will thus change. Need: What I'd like to do is have a lookup/replacement of the Account Executive Sales Code populate based on the listing to the right showing which zip codes go with which AE. Example: For example it would be If the zip code in cell 7K = cell 7Y then take the Account Executive Code Cell 7B with cell 7X. Make sense? Here's a screen shot for you. What formula do I plug in where to make this happen? Thanks in advance for the help! http://i1379.photobucket.com/albums/ah133/PDQS4/excel%20help_zpsxdlksuyp.png Quote Link to comment Share on other sites More sharing options...
Miller Posted December 16, 2015 Report Share Posted December 16, 2015 In B7 you would enter: =INDEX($X$6:$Y$13,MATCH(k7,$Y$6:$Y$13,0),1) Quote Link to comment Share on other sites More sharing options...
Turbs3000 Posted December 16, 2015 Report Share Posted December 16, 2015 What do you mean by "then take the Account Executive Code Cell 7B with cell 7X." do you mean just replace what's in column B with what's in Column X? assuming that's what you meant, first flip the zip and the AE so the zip is in X and the AE is in Y. Then it would just be: in cell B7 =vlookup(K7,$X$7:$Y$13,2,FALSE) Quote Link to comment Share on other sites More sharing options...
justinwebb Posted December 16, 2015 Report Share Posted December 16, 2015 gonna say use dat vlookup Quote Link to comment Share on other sites More sharing options...
Miller Posted December 16, 2015 Report Share Posted December 16, 2015 Rookie mcsteak The vlookup is the formulas of old, similar to a 2000 Buick lesabre. It's ugly, and, it gets you where you need to go. If you're willing to do a little work to it like the poster suggested. Indexing is the Cadillac CTSV of lookup formulas. Clean, stylish; powerful. It eliminates the need to manipulate your data or do any sorting. It eliminates opportunity for error. Quote Link to comment Share on other sites More sharing options...
Ramsey Posted December 17, 2015 Report Share Posted December 17, 2015 Rookie mcsteak The vlookup is the formulas of old, similar to a 2000 Buick lesabre. It's ugly, and, it gets you where you need to go. If you're willing to do a little work to it like the poster suggested. Indexing is the Cadillac CTSV of lookup formulas. Clean, stylish; powerful. It eliminates the need to manipulate your data or do any sorting. It eliminates opportunity for error. Miller, i need some personal training in the matters of excel. Quote Link to comment Share on other sites More sharing options...
Miller Posted December 17, 2015 Report Share Posted December 17, 2015 Miller, i need some personal training in the matters of excel. Happy to help dude. Feel free to PM or hijack this thread at will. Quote Link to comment Share on other sites More sharing options...
TTQ B4U Posted December 17, 2015 Author Report Share Posted December 17, 2015 Thanks to Miller, the world of numbers is good! Regards!! Quote Link to comment Share on other sites More sharing options...
Miller Posted December 17, 2015 Report Share Posted December 17, 2015 Thanks to Miller, the world of numbers is good! Regards!! No problem and no hard feelings on the S4 trolling. It's a nice car! ~Chancellor millz Quote Link to comment Share on other sites More sharing options...
TTQ B4U Posted December 18, 2015 Author Report Share Posted December 18, 2015 No problem and no hard feelings on the S4 trolling. Quote Link to comment Share on other sites More sharing options...
Twistedrx7 Posted December 23, 2015 Report Share Posted December 23, 2015 Second that for index and match. Glad you got it figured out Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.