SOLVED

Need help extractign values from one table to another

%3CLINGO-SUB%20id%3D%22lingo-sub-2416456%22%20slang%3D%22en-US%22%3ENeed%20help%20extractign%20values%20from%20one%20table%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2416456%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20so%20as%20you%20can%20see%20in%20the%20file%2C%20I%20would%20like%20to%20extract%20the%20values%20from%20the%20sheet%20%22Master%20-5%2C5%22%20to%20the%20sheet%20%22Jogadores%22%2C%20but%20I%20want%20to%20be%20the%20values%20corresponding%20to%20the%20exact%20name%20on%20the%20row%20and%20the%20value%20of%20the%20column%2C%20I%20have%20tried%20Index%20and%20match%20match%20but%20when%20it%20is%20time%20to%20change%20for%20a%20different%20name%20it%20just%20extracts%20the%20wrong%20one.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2416456%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-2416591%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20extractign%20values%20from%20one%20table%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2416591%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1071250%22%20target%3D%22_blank%22%3E%40goncalopalma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20couple%20of%20things%20I%20noticed%20--%3C%2FP%3E%3CP%3EThe%20%22match%22%20portion%20of%20your%20formula%20was%20trying%20to%20lookup%20the%20name%20in%20the%20original%20master%20table%20within%20the%20master%20table.%20Instead%2C%20in%20%22Jogadores%22%2C%20match%20the%20value%20there%20against%20the%20master%20table.%26nbsp%3B%20For%20example%2C%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EMATCH(Jogadores!H%242%2C'Master%20(-5%2C5)'!%24B%242%3A%24BR%242%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BSame%20for%20the%20%22abnormal%22%20data%20in%20column%20A%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EMATCH(Jogadores!%24A4%2C'Master%20(-5%2C5)'!%24A%243%3A%24A%2413%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20that%20aside%2C%20make%20sure%20your%20index%2Fmatch%20is%20in%20the%20right%20order.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EINDEX(Reference%2CRow%20Number%2C%5BColumn%20Number%5D%2C%5BArea%20Number%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20think%20you%20had%20column%2C%20then%20row%20in%20your%20original%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELastly%2C%20you%20probably%20noticed%20that%20I%20used%20absolute%20cell%20references.%26nbsp%3B%20%26nbsp%3BThis%20will%20help%20keep%20your%20formulas%20pointing%20to%20the%20right%20set%20of%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EKarl%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi so as you can see in the file, I would like to extract the values from the sheet "Master -5,5" to the sheet "Jogadores", but I want to be the values corresponding to the exact name on the row and the value of the column, I have tried Index and match match but when it is time to change for a different name it just extracts the wrong one. 

2 Replies
best response confirmed by goncalopalma (New Contributor)
Solution

@goncalopalma 

 

A couple of things I noticed --

The "match" portion of your formula was trying to lookup the name in the original master table within the master table. Instead, in "Jogadores", match the value there against the master table.  For example, 

MATCH(Jogadores!H$2,'Master (-5,5)'!$B$2:$BR$2,0)

 Same for the "abnormal" data in column A:

MATCH(Jogadores!$A4,'Master (-5,5)'!$A$3:$A$13,0)

 

All that aside, make sure your index/match is in the right order.  

INDEX(Reference,Row Number,[Column Number],[Area Number])

I think you had column, then row in your original formula.

 

Lastly, you probably noticed that I used absolute cell references.   This will help keep your formulas pointing to the right set of cells.

 

Cheers

Karl

 

Thank you so much!!!!