Forum Discussion

Gwads1968's avatar
Gwads1968
Copper Contributor
May 14, 2019
Solved

Duplicating Information Formula Help Please on Large Data Spreadsheet.

The spreadsheet attached has different Districts (column A) - Column D-K will always be the same values and comments for the same person named in columns B and C.   Basically without filtering and ...
excel
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    May 14, 2019

    Gwads1968 

    In the attached file, the formula in D12, copied down rows and across columns, is: 

    =INDEX($D$5:$K11,
    MATCH($B12&"|"&$C12,INDEX($B$5:$B11&"|"&$C$5:$C11,0),0),
    COLUMN()-3)

Twifoo's avatar
Twifoo
Silver Contributor
May 14, 2019

Gwads1968 

In the attached file, the formula in D12, copied down rows and across columns, is: 

=INDEX($D$5:$K11,
MATCH($B12&"|"&$C12,INDEX($B$5:$B11&"|"&$C$5:$C11,0),0),
COLUMN()-3)

  • Gwads1968's avatar
    Gwads1968
    Copper Contributor
    May 17, 2019

    Twifoo 

     

    Hi - Many thanks for your previous help - I have one small change and need an update on the lookup formula if possible please - previously I had the individual name separate (first name-surname) - I now will be having the Full name in one cell which has impacted your formula - if you get chance can you just make the necessary adjustment - I have tried but can't figure it out.

     

    I hope you can help - Thanking you in advance.

     
     
    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 17, 2019

      Gwads1968 

      If the lookup value is now the full name instead of the first and last names, a simple VLOOKUP would do the job. The formula in C12, copied down rows and across columns, would be: 

      =VLOOKUP($B12,$B$5:$J11,
      COLUMN()-1,0)

      See it in the attached file.

      • Gwads1968's avatar
        Gwads1968
        Copper Contributor
        May 18, 2019

        Twifoo 

         

        Thank You - This is the actual spreadsheet (sample) The cells of interest are the "Name" and then cells "I-P" - can you possibly change the look up formula to match this exact spreadsheet?

         

         

         

        Kind Regards.

         
         
  • Gwads1968's avatar
    Gwads1968
    Copper Contributor
    May 15, 2019

    Twifoo 

     

    You, Sir, are a Genius - Many Thanks for your Guidance and Formula - It works a treat.

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 15, 2019
      It's my pleasure to help you.
      • Gwads1968's avatar
        Gwads1968
        Copper Contributor
        May 15, 2019

        Twifoo 

         

        Hi - I have one small change and need an update on the lookup formula if possible please - previously I had the individual name separate (first name-surname) - I now will be having the Full name in one cell which has impacted your formula - if you get chance can you just make the necessary adjustment - I have tried but can't figure it out.

         

        Thanking you in advance.

Resources