Lookup value in table, return the cells above

[jgul]

Following from .. Lookup value in table, return the cell above

Here is one way (assuming values can only occur once in Column1:Column3)

21 03 23.xlsm
	A	B	C	D	E	F
1	Value to Search	Result:		Column1	Column2	Column3
2	abc	ipsum		hello	ipsum	filler
3	def	world		world	abc	text
4	ghi	here		def	lorem	here
5				random	word	ghi
Cell above

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(D:F,AGGREGATE(15,6,ROW(D$2:F$5)/(D$2:F$5=A2),1)-1,AGGREGATE(15,6,(COLUMN(D$2:F$5)-COLUMN(D2)+1)/(D$2:F$5=A2),1))

Hi Peter!
Thanks for that formula. But do you know a way to achive the same workflow, if there is multiple cells with same information.
Example the datas in Sheet 1 look like this. The yellow cells will be unique and will not be multiple places. But the other cells will contain simular information.

hjelp.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Unit 1, Outputs				1	2	3	4	5	6
2					Jon	Jon	Mark	Mark	Chris	Chris
3					#1	#2	#3	#4	#5	#6
4					U1	U1	U1	U1	U1	U1
5					Unit 1	Unit 1	Unit 1	Unit 1	Unit 1	Unit 1
6					Cable1.1	Cable1.2	Cable2.1	Cable2.2	Cable3.1	Cable3.11
7	Unit 2, Outputs				1	2	3	4	5	6
8					Peter	Jon	Mark	Chris	Jon	Peter
9					#7	#8	#9	#10	#11	#12
10					U2	U2	U2	U2	U2	U2
11					Unit 2	Unit 2	Unit 2	Unit 2	Unit 2	Unit 2
12					Cable1.3	Cable1.7	Cable2.3	Cable2.4	Cable3.9	Cable3.4
13	Unit 3, Outputs				1	2	3	4	5	6
14					Chris	Mark	Peter	Hans	Mark	Jon
15					#13	#14	#15	#16	#17	#18
16					U3	U3	U3	U3	U3	U3
17					Unit 3	Unit 3	Unit 3	Unit 3	Unit 3	Unit 3
18					Cable1.5	Cable1.8	Cable2.8	Cable2.6	Cable3.10	Cable3.6
Sheet1

I want to fill out to Sheet 2, that looks like this:

hjelp.xlsx
	A	B	C	D	E	F	G	H
1		Cable1	Cable1.1	Cable1.2	Cable1.3	Cable1.4	Cable1.5	Cable1.6
2			Jon	Jon	Peter		Chris
3			#1	#1	#7		#13
4			U1	U1	U2		U3
5			Unit 1	Unit 1	Unit 2		Unit 3
6			Cable1.7	Cable1.8	Cable1.9	Cable1.10	Cable1.11	Cable1.12
7			Peter	Chris
8			#7	#13
9			U2	U3
10			Unit 2	Unit 3
11
12		Cable2	Cable2.1	Cable2.2	Cable2.3	Cable2.4	Cable2.5	Cable2.6
13			Jon	Jon	Peter	Peter		Chris
14			#1	#1	#7	#7		#13
15			U1	U1	U2	U2		U3
16			Unit 1	Unit 1	Unit 2	Unit 2		Unit 3
17			Cable2.7	Cable2.8	Cable2.9	Cable2.10	Cable2.11	Cable2.12
18				Chris
19				#13
20				U3
21				Unit 3
22
23		Cable3	Cable3.1	Cable3.2	Cable3.3	Cable3.4	Cable3.5	Cable3.6
24			Jon			Peter		Chris
25			#1			#7		#13
26			U1			U2		U3
27			Unit 1			Unit 2		Unit 3
28			Cable3.7	Cable3.8	Cable3.9	Cable3.10	Cable3.11	Cable3.12
29					Peter	Chris	Jon
30					#7	#13	#1
31					U2	U3	U1
32					Unit 2	Unit 3	Unit 1
Sheet2

Cell Formulas
Range	Formula
C1,C23,C12	C1	=RIGHT($B1,20)&"."&"1"
D1,D23,D12	D1	=RIGHT($B1,20)&"."&"2"
E1,E23,E12	E1	=RIGHT($B1,20)&"."&"3"
F1,F23,F12	F1	=RIGHT($B1,20)&"."&"4"
G1,G23,G12	G1	=RIGHT($B1,20)&"."&"5"
H1,H23,H12	H1	=RIGHT($B1,20)&"."&"6"
C2:H2,C7:H7,C24:H24,C29:H29,C13:H13,C18:H18	C2	=IFERROR(INDEX(Sheet1!$E:$J,AGGREGATE(15,6,ROW(Sheet1!$E$1:$J$500)/(Sheet1!$E$1:$J$500=C1),1)-4,AGGREGATE(15,6,(COLUMN(Sheet1!$E$1:$J$500)-COLUMN(Sheet1!$E$1:$J$500)+1)/(Sheet1!$E$1:$J$500=C1),1)),"")
C3:H3,C8:H8,C25:H25,C30:H30,C14:H14,C19:H19	C3	=IFERROR(INDEX(Sheet1!$E:$J,AGGREGATE(15,6,ROW(Sheet1!$E$1:$J$500)/(Sheet1!$E$1:$J$500=C1),1)-3,AGGREGATE(15,6,(COLUMN(Sheet1!$E$1:$J$500)-COLUMN(Sheet1!$E$1:$J$500)+1)/(Sheet1!$E$1:$J$500=C1),1)),"")
C4:H4,C9:H9,C26:H26,C31:H31,C15:H15,C20:H20	C4	=IFERROR(INDEX(Sheet1!$E:$J,AGGREGATE(15,6,ROW(Sheet1!$E$1:$J$500)/(Sheet1!$E$1:$J$500=C1),1)-2,AGGREGATE(15,6,(COLUMN(Sheet1!$E$1:$J$500)-COLUMN(Sheet1!$E$1:$J$500)+1)/(Sheet1!$E$1:$J$500=C1),1)),"")
C5:H5,C10:H10,C27:H27,C32:H32,C16:H16,C21:H21	C5	=IFERROR(INDEX(Sheet1!$E:$J,AGGREGATE(15,6,ROW(Sheet1!$E$1:$J$500)/(Sheet1!$E$1:$J$500=C1),1)-1,AGGREGATE(15,6,(COLUMN(Sheet1!$E$1:$J$500)-COLUMN(Sheet1!$E$1:$J$500)+1)/(Sheet1!$E$1:$J$500=C1),1)),"")
C6,C28,C17	C6	=RIGHT($B1,20)&"."&"7"
D6,D28,D17	D6	=RIGHT($B1,20)&"."&"8"
E6,E28,E17	E6	=RIGHT($B1,20)&"."&"9"
F6,F28,F17	F6	=RIGHT($B1,20)&"."&"10"
G6,G28,G17	G6	=RIGHT($B1,20)&"."&"11"
H6,H28,H17	H6	=RIGHT($B1,20)&"."&"12"

I want all the green cells to be filled out automatically with the search-word in the Grey cell thats right above.

The formula as it is now only fills the first name from sheet 1, in all cells. It looks like it finds the right row but it will return the first column every time.
I need it to be flexible so all cells can be searched for in every formula.
Can someone help?

 

[Peter_SSs]

This was a significantly different question so I have split it to a new thread.
Further, you have a different Excel version to that original OP so have different resources available.

See if this does what you want. The formulas in the green sections only need to go in the top row of each of those sections.
I have also suggested a new formula for the grey sections of Sheet2. These only need to go in column C and the same formula can be copied from C1 to C6, C12, C17 etc

jgul.xlsm
	E	F	G	H	I	J
1	1	2	3	4	5	6
2	Jon	Jon	Mark	Mark	Chris	Chris
3	1	2	3	4	5	6
4	U1	U1	U1	U1	U1	U1
5	Unit 1	Unit 1	Unit 1	Unit 1	Unit 1	Unit 1
6	Cable1.1	Cable1.2	Cable2.1	Cable2.2	Cable3.1	Cable3.11
7	1	2	3	4	5	6
8	Peter	Jon	Mark	Chris	Jon	Peter
9	7	8	9	10	11	12
10	U2	U2	U2	U2	U2	U2
11	Unit 2	Unit 2	Unit 2	Unit 2	Unit 2	Unit 2
12	Cable1.3	Cable1.7	Cable2.3	Cable2.4	Cable3.9	Cable3.4
13	1	2	3	4	5	6
14	Chris	Mark	Peter	Hans	Mark	Jon
15	13	14	15	16	17	18
16	U3	U3	U3	U3	U3	U3
17	Unit 3	Unit 3	Unit 3	Unit 3	Unit 3	Unit 3
18	Cable1.5	Cable1.8	Cable2.8	Cable2.6	Cable3.10	Cable3.6
19
Sheet1

jgul.xlsm
	C	D	E	F	G	H
1	Cable1.1	Cable1.2	Cable1.3	Cable1.4	Cable1.5	Cable1.6
2	Jon	Jon	Peter		Chris
3	1	2	7		13
4	U1	U1	U2		U3
5	Unit 1	Unit 1	Unit 2		Unit 3
6	Cable1.7	Cable1.8	Cable1.9	Cable1.10	Cable1.11	Cable1.12
7	Jon	Mark
8	8	14
9	U2	U3
10	Unit 2	Unit 3
11
12	Cable2.1	Cable2.2	Cable2.3	Cable2.4	Cable2.5	Cable2.6
13	Mark	Mark	Mark	Chris		Hans
14	3	4	9	10		16
15	U1	U1	U2	U2		U3
16	Unit 1	Unit 1	Unit 2	Unit 2		Unit 3
17	Cable2.7	Cable2.8	Cable2.9	Cable2.10	Cable2.11	Cable2.12
18		Peter
19		15
20		U3
21		Unit 3
22
23	Cable3.1	Cable3.2	Cable3.3	Cable3.4	Cable3.5	Cable3.6
24	Chris			Peter		Jon
25	5			12		18
26	U1			U2		U3
27	Unit 1			Unit 2		Unit 3
28	Cable3.7	Cable3.8	Cable3.9	Cable3.10	Cable3.11	Cable3.12
29			Jon	Mark	Chris
30			11	17	6
31			U2	U3	U1
32			Unit 2	Unit 3	Unit 1
33
Sheet2

Cell Formulas
Range	Formula
C1:H1,C28:H28,C23:H23,C17:H17,C12:H12,C6:H6	C1	="Cable"&COUNTA(B$1:B1)&"."&SEQUENCE(,6,1+6*(B1=""))
E29:G32,C29:D29,H29,H24:H27,F24:F27,D24:E24,G24,C24:C27,D18:D21,C18,E18:H18,H13:H16,G13,C13:F16,E7:H7,C7:D10,G2:G5,F2,H2,C2:E5	C2	=LET(c,CHOOSECOLS(Sheet1!$E$2:$J$200,CONCAT(IF(Sheet1!$E$2:$J$200=C1,Sheet1!$E$1:$J$1,""))),IFERROR(INDEX(c,SEQUENCE(4,,MATCH(C1,c,0)-4)),""))
Dynamic array formulas.

 

[jgul]

Thank you so much for giving your help! We are on a good track to a solution now

I have one "small" adjustment, maybe i was a bit diffuse in the question.
The name "Cable" i also want to be flexible. We use different types of cables, the name can be changing and not only "Cable". I really want to write the name for each cable manually in Sheet 2, the B colomn.
So is it possible to in the Sheet 2 use the B1...B12...B23..cell as the key-word, and still be able to copy to get more tables like B45, B56 ans so on..?
And as a bonus question (thats maybe changing with a new formula), here B34 is empty, its filling strange names from C to H and giving "fake" results, is this possible to avoid?

Thank you so much again!

 

[Peter_SSs]

Don't really understand. What about the sample data and expected results with XL2BB so it can be copied for testing? Then explain again in relation to that data/results.

 

[jgul]

Sorry, here.
Sheet 1 is working fine.

So "Sheet 2" contains boxes of 12 outputs. I want to give the names to these boxes manually in "sheet 2".
The name will be in cell B1 + 12 + 23... The name will be changing and if B1= "Name" i want C1 to H1 be like Name.1 , Name.2 up to Name.12. If B1= "DMX" i want C1 to H1 be like DMX.1 , DMX.2 up to DMX.12 have Like i had with my formula, (this can of course be another formula if thats better for a solution). Also if the sequence formula can be adjusted easy to give only 3 or 4 columns it wold be nice. I have added an example for these at the bottom.
I want to be able to copy and add more boxes if needed. One time i use 3, and other times i use maybe 20 boxes. It would also be great if

hjelp.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Unit 1, Outputs				1	2	3	4	5	6
2					Jon	Jon	Mark	Mark	Chris	Chris
3					#1	#2	#3	#4	#5	#6
4					U1	U1	U1	U1	U1	U1
5					Unit 1	Unit 1	Unit 1	Unit 1	Unit 1	Unit 1
6					LK 1.1	LK 1.3	DMX 1.1	DMX 3.1	Cable3.1	Cable3.11
7	Unit 2, Outputs				1	2	3	4	5	6
8					Peter	Jon	Mark	Chris	Jon	Peter
9					#7	#8	#9	#10	#11	#12
10					U2	U2	U2	U2	U2	U2
11					Unit 2	Unit 2	Unit 2	Unit 2	Unit 2	Unit 2
12					LK 11.3	DMX 11.7	LK 12.3	Cable2.4	LK 13.9	DMX 13.4
13	Unit 3, Outputs				1	2	3	4	5	6
14					Chris	Mark	Peter	Hans	Mark	Jon
15					#13	#14	#15	#16	#17	#18
16					U3	U3	U3	U3	U3	U3
17					Unit 3	Unit 3	Unit 3	Unit 3	Unit 3	Unit 3
18					Cable1.5	DMX 11.8	Cable2.8	LK 12.6	Cable3.10	DMX 13.6
19
Sheet1

hjelp.xlsx
	A	B	C	D	E	F	G	H	I
1		LK 1	Cable1.1	Cable1.2	Cable1.3	Cable1.4	Cable1.5	Cable1.6
2							Chris
3							#13
4							U3
5							Unit 3
6			Cable1.7	Cable1.8	Cable1.9	Cable1.10	Cable1.11	Cable1.12
7
8
9
10
11
12		DMX A	Cable2.1	Cable2.2	Cable2.3	Cable2.4	Cable2.5	Cable2.6
13						Chris
14						#10
15						U2
16						Unit 2
17			Cable2.7	Cable2.8	Cable2.9	Cable2.10	Cable2.11	Cable2.12
18				Peter
19				#15
20				U3
21				Unit 3
22
23		Cable 1	Cable3.1	Cable3.2	Cable3.3	Cable3.4	Cable3.5	Cable3.6
24
25
26
27
28			Cable3.7	Cable3.8	Cable3.9	Cable3.10	Cable3.11	Cable3.12
29						Mark	Chris
30						#17	#6
31						U3	U1
32						Unit 3	Unit 1
33
34			Cable3.7	Cable3.8	Cable3.9	Cable3.10	Cable3.11	Cable3.12
35						Mark	Chris
36						#17	#6
37						U3	U1
38						Unit 3	Unit 1
39			Cable3.7	Cable3.8	Cable3.9	Cable3.10	Cable3.11	Cable3.12
40						Mark	Chris
41						#17	#6
42						U3	U1
43						Unit 3	Unit 1
44
45			Cable3.7	Cable3.8	Cable3.9	Cable3.10	Cable3.11	Cable3.12
46						Mark	Chris
47						#17	#6
48						U3	U1
49						Unit 3	Unit 1
50			Cable3.7	Cable3.8	Cable3.9	Cable3.10	Cable3.11	Cable3.12
51						Mark	Chris
52						#17	#6
53						U3	U1
54						Unit 3	Unit 1
55
56			Cable3.7	Cable3.8	Cable3.9	Cable3.10	Cable3.11	Cable3.12
57						Mark	Chris
58						#17	#6
59						U3	U1
60						Unit 3	Unit 1
61			Cable3.7	Cable3.8	Cable3.9	Cable3.10	Cable3.11	Cable3.12
62						Mark	Chris
63						#17	#6
64						U3	U1
65						Unit 3	Unit 1
66
67		4 Box	Cable4.1	Cable4.2	Cable4.3	Cable4.4
68
69
70
71
72
73		3 Box	Cable5.1	Cable5.2	Cable5.3
74
75
76
77
78
Sheet2

Cell Formulas
Range	Formula
C1:H1,C61:H61,C56:H56,C50:H50,C45:H45,C39:H39,C34:H34,C28:H28,C23:H23,C17:H17,C12:H12,C6:H6	C1	="Cable"&COUNTA(B$1:B1)&"."&SEQUENCE(,6,1+6*(B1=""))
C2,C74,C68,C57,C46,C35,C24,C13	C2	=LET(c,CHOOSECOLS(Sheet1!$E$2:$J$200,CONCAT(IF(Sheet1!$E$2:$J$200=C1,Sheet1!$E$1:$J$1,""))),IFERROR(INDEX(c,SEQUENCE(4,,MATCH(C1,B1,0)-4)),""))
D2:F2,H2,D74:E74,D68:F68,F62:G65,C62:E62,H62,F57:G60,D57:E57,H57,F51:G54,C51:E51,H51,F46:G49,D46:E46,H46,F40:G43,C40:E40,H40,F35:G38,D35:E35,H35,F29:G32,C29:E29,H29,D24:H24,D18:D21,C18,E18:H18,F13:F16,D13:E13,G13:H13,C7:H7,G2:G5	D2	=LET(c,CHOOSECOLS(Sheet1!$E$2:$J$200,CONCAT(IF(Sheet1!$E$2:$J$200=D1,Sheet1!$E$1:$J$1,""))),IFERROR(INDEX(c,SEQUENCE(4,,MATCH(D1,c,0)-4)),""))
C67:F67	C67	="Cable"&COUNTA(B$1:B67)&"."&SEQUENCE(,4,1+6*(B67=""))
C73:E73	C73	="Cable"&COUNTA(B$1:B73)&"."&SEQUENCE(,3,1+6*(B73=""))
Dynamic array formulas.

 

[Peter_SSs]

Would have been better in Sheet2 to put the expected results as requested, rather than the previous formulas that obviously are not producing the results that you want.

What would you expect in C34 etc where B34 is blank? Or will B34 really have something in it?

Why do the bottom two sections in Sheet2 have less rows and less columns? Will those sections always be that size & therefore require different formulas to the sections above?

 

[jgul]

Would have been better in Sheet2 to put the expected results as requested, rather than the previous formulas that obviously are not producing the results that you want.

What would you expect in C34 etc where B34 is blank? Or will B34 really have something in it?

Why do the bottom two sections in Sheet2 have less rows and less columns? Will those sections always be that size & therefore require different formulas to the sections above?

Yes so if B34 is empty it would be nice if then C34, D34, E34 also is empty. This is a trifle, because I just can delete these boxes, but it could look strange. If there is results when the box's name is empty. (It will be other people than me using this) If this is hard for the formula to calculate when blank, I can just ignore this and go with it.


The 3 and 4 boxes in the bottom will always have that size and can use one/two different formulas than the 12 boxes. As an template I can have one of each type (12,4,3) and just copy as many as I need.