Home

Need to create a formula that uses a key to fill in multiple cells

%3CLINGO-SUB%20id%3D%22lingo-sub-571091%22%20slang%3D%22en-US%22%3ENeed%20to%20create%20a%20formula%20that%20uses%20a%20key%20to%20fill%20in%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-571091%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20if%20this%20is%20super%20easy%2C%20I'm%20not%20a%20big%20excel%20user%20but%20my%20boss%20wants%20me%20to%20do%20something%20involving%205000%20user%20names%20which%20I%20don't%20want%20to%20do%20by%20hand...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20I%20need%20to%20fill%20in%20the%20first%20table%20to%20make%20it%20look%20like%20the%20table%20below%20it%20(A23)%20using%20the%20key%20I%20made%20(K1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20know%20something%20I%20can%20use%20for%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-571091%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-571128%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20create%20a%20formula%20that%20uses%20a%20key%20to%20fill%20in%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-571128%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20D2%20enter%20formula%3A%3C%2FP%3E%3CP%3E%3DINDEX(L%3AL%2CMATCH(%24A2%2C%24K%3A%24K%2C0))%3C%2FP%3E%3CP%3EDrag%20that%20formula%20down%20for%20all%20your%20rows%2C%20and%20across%20for%20all%20your%20columns%20with%20headers%20that%20are%20now%20empty.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-571215%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20create%20a%20formula%20that%20uses%20a%20key%20to%20fill%20in%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-571215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342713%22%20target%3D%22_blank%22%3E%40Kim_Wennerberg%3C%2FA%3E%26nbsp%3BMadam%2C%20you%20are%20GD%20wizard.%20Thank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-571268%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20create%20a%20formula%20that%20uses%20a%20key%20to%20fill%20in%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-571268%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20welcome.%26nbsp%3B%20BTW%2C%20not%20important%20or%20relevant%2C%20but%20some%20people%20with%20name%20of%20Kim%20are%20%22sir%22.%26nbsp%3B%20That%20would%20be%20me.%3C%2FP%3E%3CP%3EThat%20MATCH()%20function%20is%20often%20part%20of%20a%20rich%20substitute%20for%20the%20VLOOKUP()%20function.%26nbsp%3B%20It%20will%20do%20you%20well%20to%20usuially%20use%20INDEX()%20with%20MATCH()%20instead%20of%20VLOOKUP()%2C%20even%20though%20the%20formula%20looks%20intimidating%20and%20a%20bit%20cryptic.%26nbsp%3B%20Google%20search%20on%20Excel%20%22MATCH%20function%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E
lucas1wj
New Contributor

Sorry if this is super easy, I'm not a big excel user but my boss wants me to do something involving 5000 user names which I don't want to do by hand...

 

Basically I need to fill in the first table to make it look like the table below it (A23) using the key I made (K1)

 

Anyone know something I can use for this?

3 Replies

In D2 enter formula:

=INDEX(L:L,MATCH($A2,$K:$K,0))

Drag that formula down for all your rows, and across for all your columns with headers that are now empty.

@Kim_Wennerberg Madam, you are GD wizard. Thank you so much!

You are welcome.  BTW, not important or relevant, but some people with name of Kim are "sir".  That would be me.

That MATCH() function is often part of a rich substitute for the VLOOKUP() function.  It will do you well to usuially use INDEX() with MATCH() instead of VLOOKUP(), even though the formula looks intimidating and a bit cryptic.  Google search on Excel "MATCH function".