Look for Cell Data in Cell Array, and Cell Data in Row Array and if match show this Cell Data

[Iccreamann21]

Look for Cell Data in Cell Array, and Cell Data in Row Array and if match show this Cell Data

LPA findings sheet - 2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1	Name2	ID	Completion time	STATION	Operator Name	Number	Should Be		Month	January																									February
2	John	1710	7/2/24 8:58:28	Engine Line 060LH	Malcolm	12	look for A2, in column I:I, and then look for F2 in the row that A2 was found and then show the Week number in row 3		Week	Week 1					Week 2					Week 3					Week 4					Week 5					Week 6					Week 7					Week 8					Week 9
3	Bob	1711	7/2/24 8:58:28	Main Line 2 Kitting	Erica	59	Week 6		Dates	1/1	1/2	1/3	1/4	1/5	1/8	1/9	1/10	1/11	1/12	1/15	1/16	1/17	1/18	1/19	1/22	1/23	1/24	1/25	1/26	1/29	1/30	1/31	2/1	2/2	2/5	2/6	2/7	2/8	2/9	2/12	2/13	2/14	2/15	2/16	2/19	2/20	2/21	2/22	2/23	2/26	2/27	2/28	2/29	3/1
4	Mark	1712	7/2/24 8:58:28	Main Line 2 020LH	Brittany	60	1st Week Seen
5	Mark	1713	7/2/24 8:58:28	Main Line 1 020LH	Jay	61			James					1																									11
6	Mark	1714	7/2/24 8:58:28	Main Line 5 020LH	Adam	62			John					12																									12
7	Dave	1715	7/2/24 8:58:28	Main Line 8 020LH	Malcolm	63			Bill					5																									15
8	Dave	1716	7/2/24 8:58:28	Main Line 2 020RH	Adam	7			Bob					2																									59
9	Justin	1717	7/2/24 8:58:28	Main Line 2 030LH	Josh	30			Josh					9																									19
10	Harold	1718	7/2/24 8:58:28	Main Line 2 030RH	Jay	31			Dave					63					x					63					x					1					2					3					7					15
11	Mark	1719	7/2/24 8:58:28	Main Line 2 020LH	Brittany	60	2nd Week Seen		Mark					62					x					61					x					60					61					55					59					12
12	Mark	1720	7/2/24 8:58:28	Main Line 2 020LH	Brittany	60	3rd Week Seen		Tim					3					x					24					x					90					20					42					64					86
13									Dalton					3					x					25					x					91					21					43					65					87
14									Harold					5					x					26					x					92					22					44					66					88
15									Justin					6					x					27					x					93					30					45					67					89
Sheet2

 

[hagia_sofia]

I am not sure whether I understand it correctly. This should return the corresponding week number:

=LET(
headers,TOROW(IFERROR(EXPAND(TOROW($J$2:$BB$2,1),5),TOROW($J$2:$BB$2,1)),,1),
row,INDEX($J$5:$BB$15,XMATCH(A2,$I$5:$I$15),0),
XLOOKUP(F2,row,headers))

But a) what is the meaning of yellow cells, b) why are some cells of "should be" blank? c) what do you mean by "Week number in row 3" - do you mean row 2?

 

[Iccreamann21]

I am not sure whether I understand it correctly. This should return the corresponding week number:

=LET(
headers,TOROW(IFERROR(EXPAND(TOROW($J$2:$BB$2,1),5),TOROW($J$2:$BB$2,1)),,1),
row,INDEX($J$5:$BB$15,XMATCH(A2,$I$5:$I$15),0),
XLOOKUP(F2,row,headers))

But a) what is the meaning of yellow cells, b) why are some cells of "should be" blank? c) what do you mean by "Week number in row 3" - do you mean row 2?

a) yellow cells mean nothing. i was just highlighting those cells to show "Mark" has completed number "60" three different times

b) i left a few cells blank in "Should be" for no reason

c) yes my mistake i row 2

The problem I am running into is Mark for example, if he completes number 60 during week 1, week 15, and week 26... it will only ever show Week 1.. Is it possible to show week 1 in G4, Week 15 in G11, Week 26 in G12?

 

[hagia_sofia]

Many thanks for the feedback. Please test this and let me know if it works or not:

=LET(
headers,TOROW(IFERROR(EXPAND(TOROW($K$2:$BC$2,1),5),TOROW($K$2:$BC$2,1)),,1),
row,INDEX($K$5:$BC$15,XMATCH(A2,$J$5:$J$15),0),
count,COUNTIFS($A$2:A2,A2,$F$2:F2,F2),
CHOOSECOLS(FILTER(headers,row=F2),count))

 

[Iccreamann21]

Thank you!