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)

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.

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

      Gwads1968 

      In the attached version of your file, the formula in I8, copied down rows and across columns, is: 

      =VLOOKUP($C8,
      $C$4:$P7,
      COLUMN()-2,0)

      Note that Row 7 in the table_array argument is relative so that it will adjust to the row above the formula cell whenever the formula is copied down rows. Conversely, the 2 column reduction of the col_index_num argument represents the 2 Columns preceding the table array, which are Columns A and B. 

Resources