January 23, 2020
I have 3 columns of information with headings by 3 sections down (basically a 3x3 grid of information with headings over each section) on sheet2
On Sheet1 I have a list of names where I need to search for the name in the 3x3 grid and return the heading it is under
I need to know what Heading Dianne is under. I will also have all the names listed on Sheet1 where I need to find the corresponding heading.
Plus if John moves from Heading 1 to Heading 3, the information will be updated on sheet1
VIP
Trusted Members
December 7, 2016
Trusted Members
December 20, 2019
The layout of the data isnt great, but i have knocked up the attached with a few helper columns (these can probaly be consolidated at some point) and i have also moved the tables to the main worksheet for ease of reference but these could be moved back to the other sheet.
I am sure there other options
Purfleet
Answers Post
Active Member
September 14, 2018
October 5, 2010
Hi Sara,
I'd highly recommend that your data is organised into a tabular format and create a table so that reporting and formulas can work more easily. I've done this on Sheet3 (see attached).
You can now use VLOOKUP or XLOOKUP to return the color associated with each person's name. However the data on Sheet1 is now just a duplicate of the table on Sheet3 so perhaps in itself isn't that useful.
You can create the reports on Sheet2 by using Pivot Tables or the FILTER function.
Regards
Phil
VIP
Trusted Members
June 25, 2016
Hi Sara
See attached. It is a crazy ARRAY formula I modified from here:
https://www.myonlinetraininghu.....ng-a-value
Good luck
Trusted Members
Moderators
November 1, 2018
This array formula is slightly shorter, but very dependent on the layout of the source data sheet! (Must start in row 1, and be 5x3 blocks with a blank row in between, as in the example):
=INDEX(Sheet2!A:C,INT(MAX(MMULT(TRANSPOSE(ROW(Sheet2!$A$1:$A$17)),--(Sheet2!$A$1:$C$17=A2)))/6)*6+1,MAX(MMULT(--(Sheet2!$A$1:$C$17=A2),TRANSPOSE(COLUMN(Sheet2!$A$1:$C$1)))))
Active Member
September 14, 2018
Trusted Members
December 20, 2019
You would need to recreate the formula's referencing the second sheet, or as a work around, paste on to a new sheet and 'paste as link' from there you can either hide any columns you don't want to see or delete them or if you want to keep the data permanently 'paste values'
See attached
Trusted Members
December 20, 2019
You want to add more colours with more names?
This is where is gets difficult with your data if you want to add more columns
=IFERROR(LEFT(CELL("address",INDEX($N$1:$P$17, MATCH($A2,$N$1:$N$17,0),1)),2), IFERROR(LEFT(CELL("address", INDEX($N$1:$P$17,MATCH($A2,$O$1:$O$17,0),2)),2), LEFT(CELL("address", INDEX($N$1:$P$17, MATCH($A2,$P$1:$P$17,0),3)),2)))
Each 'iferror' highlighted above is checking for the name in each column, so it looks in N and if not found, looks in O and then P, as soon as it finds the name it stops, therefore if you want to add more names and columns you will need to add the highlighted part for each additional column.
=IFERROR(LEFT(CELL("address", INDEX($N$1:$N$17,MATCH($A2,$N$1:$N$17,0),1)),2), IFERROR(LEFT(CELL("address", INDEX($O$1:$O$17, MATCH($A2,$O$1:$O$17,0),1)),2), IFERROR(LEFT(CELL("address", INDEX($P$1:$P$17, MATCH($A2,$P$1:$P$17,0),1)),2),"")))
Above i have simplified the formula slightly so you can see in the first iferror column N is referenced, and the second is O and so on, copy the part in red and paste it in at the end of the formula just before the double quotes and then change the column it is looking at (q in my example)
You will also need to do the same thing in the row column
=IFERROR(MATCH($A10,$N$1:$N$18,0), IFERROR(MATCH($A10,$O$1:$O$18,0), IFERROR(MATCH($A10,$P$1:$P$18,0), IFERROR(MATCH($A10,$Q$1:$Q$18,0),""))))
1 Guest(s)