How to lookup for one criteria and return data that aren´t in the same main table (columns and rows) below?

[Lacan]

Hello Guys,

Have this challenge how to lookup for one criteria and return data that aren´t in the same table (columns and rows) below?

With this sheet structure the goal is to change data validation in Cell P3 and to return the data corresponding in columns B and C like the example in range P6:Q10.

Can you help please?

Any question feel free to ask.

As always eternal gratitude.

Teste.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R		T
1
2
3		A	D09			299 Lx - Amadora (MÁgua - Sul)										300 Lx - Amadora (MÁgua - Norte)
4		B	D07
5		C	D07
6		D	D05													F	D08
7		E	D02													G	D05
8																H	D05
9																I	D03
10																J	D02
11
12
13
14		F	D08			300 Lx - Amadora (MÁgua - Norte)
15		G	D05
16		H	D05
17		I	D03
18		J	D02
19
20
21
22
23
24
25		K	D09			301 Lx - Amadora (Venteira)
26		L	D09
27		M	D09
28		N	D07
29		O	D07
30		P	D06
31		Q	D04
32		R	D04
33		S	D04
34		T	D04
35		U	D02
36
37
38
39
40		V	D10			302 Lx - Amadora (Alfragide, ÁLivres)
41		X	D09
42		Y	D09
43		Z	D08
44
45
46
47
48
49
50
51
52
53
54
55
PLANNING

Cells with Data Validation
Cell	Allow	Criteria
P3	List	=$S$3:$S$6

 

[Misca]

Try something like:
=OFFSET(INDEX($F$1:$F$30,MATCH($P$3,$F$1:$F$30,0)),0,-4)
in P6 and then adjust the last two parameters for the rows and columns.

The hardcoded parameters can be easily replaced with something like ROW- / ROWS- / COLUMN-/ COLUMNS-functions as well but I decided to use the hardcoded numbers just to make the formula easier to read.

 

[Peter_SSs]

In your version of Excel 365 do you have the SEQUENCE function and the LET function?

If so, try the formula in P6 only and the other results should automatically 'spill' across and down to other required cells.

Lacan.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		A	D09			299 Lx - Amadora (MÁgua - Sul)										300 Lx - Amadora (MÁgua - Norte)
4		B	D07
5		C	D07
6		D	D05													F	D08
7		E	D02													G	D05
8																H	D05
9																I	D03
10																J	D02
11
12
13
14		F	D08			300 Lx - Amadora (MÁgua - Norte)
15		G	D05
16		H	D05
17		I	D03
18		J	D02
19
20
21
22
23
24
25		K	D09			301 Lx - Amadora (Venteira)
26		L	D09
27		M	D09
28		N	D07
29		O	D07
30		P	D06
31		Q	D04
32		R	D04
33		S	D04
34		T	D04
35		U	D02
36
37
38
39
40		V	D10			302 Lx - Amadora (Alfragide, ÁLivres)
41		X	D09
42		Y	D09
43		Z	D08
Sheet1

Cell Formulas
Range	Formula
P6:Q10	P6	=LET(R_1,MATCH($P3,$F:$F,0),INDEX(B:C,SEQUENCE(MATCH(TRUE,INDEX(B:B,R_1):B1000="",0)-1,,R_1),{1,2}))
Dynamic array formulas.

If you happen to have the SEQUENCE function but not the LET function then try this in P6 instead.

=INDEX(B:C,SEQUENCE(MATCH(TRUE,INDEX(B:B,MATCH($P3,$F:$F,0)):B1000="",0)-1,,MATCH($P3,$F:$F,0)),{1,2})

 

[Lacan]

In your version of Excel 365 do you have the SEQUENCE function and the LET function?

If so, try the formula in P6 only and the other results should automatically 'spill' across and down to other required cells.

Lacan.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		A	D09			299 Lx - Amadora (MÁgua - Sul)										300 Lx - Amadora (MÁgua - Norte)
4		B	D07
5		C	D07
6		D	D05													F	D08
7		E	D02													G	D05
8																H	D05
9																I	D03
10																J	D02
11
12
13
14		F	D08			300 Lx - Amadora (MÁgua - Norte)
15		G	D05
16		H	D05
17		I	D03
18		J	D02
19
20
21
22
23
24
25		K	D09			301 Lx - Amadora (Venteira)
26		L	D09
27		M	D09
28		N	D07
29		O	D07
30		P	D06
31		Q	D04
32		R	D04
33		S	D04
34		T	D04
35		U	D02
36
37
38
39
40		V	D10			302 Lx - Amadora (Alfragide, ÁLivres)
41		X	D09
42		Y	D09
43		Z	D08
Sheet1

Cell Formulas
Range	Formula
P6:Q10	P6	=LET(R_1,MATCH($P3,$F:$F,0),INDEX(B:C,SEQUENCE(MATCH(TRUE,INDEX(B:B,R_1):B1000="",0)-1,,R_1),{1,2}))
Dynamic array formulas.

If you happen to have the SEQUENCE function but not the LET function then try this in P6 instead.

=INDEX(B:C,SEQUENCE(MATCH(TRUE,INDEX(B:B,MATCH($P3,$F:$F,0)):B1000="",0)-1,,MATCH($P3,$F:$F,0)),{1,2})

Thank you very much Peter_SSs.

In my PC have office 365 version and also both functions but not in my laptop office version.

The function helped by Misca works perfect.

Thanks again for the help.

 

[Lacan]

Try something like:
=OFFSET(INDEX($F$1:$F$30,MATCH($P$3,$F$1:$F$30,0)),0,-4)
in P6 and then adjust the last two parameters for the rows and columns.

The hardcoded parameters can be easily replaced with something like ROW- / ROWS- / COLUMN-/ COLUMNS-functions as well but I decided to use the hardcoded numbers just to make the formula easier to read.

Works Perfect!!!!

Thank you very much Misca.