Hello Sara,

It would be great if you can upload a sample file showing us the data and structure you have, it will be a lot easier for us to give you a valid reply.

Br,
Anders

Sorry about that..please see attached file

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

Look this. Maybe it can help you

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

Thanks for all your help I will try these and see if they work with my actual data.

Unfortunately the tabular format will not work as the 3x3 grid is the format that we need to use.

Hi Sara

See attached. It is a crazy ARRAY formula I modified from here:

https://www.myonlinetraininghub.com/excel-find-column-containing-a-value

Good luck

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)))))

There was an error in the code. Please, see that. 

Purfleet.....yours worked up until tried to recreate the formulas to reference sheet2.

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

Hi me again, formula works amazing job....question though how do I adjust the formula if I was to add 5 columns to the beginning of the information sheet (Sheet 2 ... where the boxes are)

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),""))))