May 14 2019 11:47 AM
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 dragging the data under the candidate name "which is time consuming" (and as the original spreadsheet has over 3000 names on it) - Is there a formula I could enter so the spreadsheet recognises the name of the person and once one line is complete it "finds" and "populates" the same name "with the same information" in other districts.
I hope that makes sense.
May 14 2019 09:35 PM
SolutionIn 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)
May 15 2019 12:59 AM
May 15 2019 01:20 AM
May 15 2019 10:52 AM
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.
May 17 2019 05:21 AM
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.
May 17 2019 09:25 PM
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.
May 18 2019 01:40 PM
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.
May 20 2019 03:02 AM
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.
May 14 2019 09:35 PM
SolutionIn 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)