Looking to use unique identifier to fill out remaining entries

%3CLINGO-SUB%20id%3D%22lingo-sub-1742230%22%20slang%3D%22en-US%22%3ELooking%20to%20use%20unique%20identifier%20to%20fill%20out%20remaining%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1742230%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Two.PNG%22%20style%3D%22width%3A%20720px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223922i46AED26BD0E1FE68%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Two.PNG%22%20alt%3D%22Two.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22One.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223921i35ED08F73EDCB519%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22One.PNG%22%20alt%3D%22One.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20these%20two%20workbooks.%20Previously%20I%20have%20used%20INDEX%20%2F%20MATCH%20(5x%20times)%20to%20get%20the%20desired%20effect%20that%20I%20want%2C%20is%20there%20a%20specific%20set%20of%20formulas%20which%20I%20can%20use%20to%20get%20multiple%20values%20from%20the%20member%20number%20to%20get%20the%20five%20entries%20which%20I%20need%20to%20fill%20out.%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1742230%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-1742422%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20to%20use%20unique%20identifier%20to%20fill%20out%20remaining%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1742422%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F801989%22%20target%3D%22_blank%22%3E%40newtonlewillows%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20you%20need%20to%20fill%20data%20into%20the%205%20columns%20to%20the%20right%20of%20each%20ID.%20Not%20sure%20why%20INDEX%2FMATCH%20or%20similar%20doesn't%20work%20-%20formula%20will%20be%20populate%20within%20the%20column%20automatically%20and%20with%20proper%20designed%20formula%20you%20only%20need%20to%20drag%20it%20to%20the%20right%20to%20fill%20other%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1742548%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20to%20use%20unique%20identifier%20to%20fill%20out%20remaining%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1742548%22%20slang%3D%22en-US%22%3EINDEX%2FMATCH%20does%20work%2C%20but%20I%20have%20to%20do%20it%205%20times%20for%20each%20column%2C%20is%20there%20are%20way%20of%20doing%20this%20only%20once%3F%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Two.PNGOne.PNG

 

I have these two workbooks. Previously I have used INDEX / MATCH (5x times) to get the desired effect that I want, is there a specific set of formulas which I can use to get multiple values from the member number to get the five entries which I need to fill out. Thank you. 

3 Replies
Highlighted

@newtonlewillows 

I guess you need to fill data into the 5 columns to the right of each ID. Not sure why INDEX/MATCH or similar doesn't work - formula will be populate within the column automatically and with proper designed formula you only need to drag it to the right to fill other columns.

Highlighted
INDEX/MATCH does work, but I have to do it 5 times for each column, is there are way of doing this only once?
Highlighted

@newtonlewillows 

Afraid I didn't catch, copy/paste formula to another 5 columns is just few seconds second.

 

I assume formula is like

=INDEX(Table1[[Forename]:[Zip Code]],
  MATCH(Table2[@[MemberNo]:[MemberNo]],Table1[[MemberNo]:[MemberNo]],0),
  COLUMN()-COLUMN(Table2[[#Headers],[MemberNo]]) )

another columns are empty at the beginning, then with copy/paste they shall be filled (don't drag)