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

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
228
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRT
1
2
3AD09299 Lx - Amadora (MÁgua - Sul)300 Lx - Amadora (MÁgua - Norte)
4BD07
5CD07
6DD05FD08
7ED02GD05
8HD05
9ID03
10JD02
11
12
13
14FD08300 Lx - Amadora (MÁgua - Norte)
15GD05
16HD05
17ID03
18JD02
19
20
21
22
23
24
25KD09301 Lx - Amadora (Venteira)
26LD09
27MD09
28ND07
29OD07
30PD06
31QD04
32RD04
33SD04
34TD04
35UD02
36
37
38
39
40VD10302 Lx - Amadora (Alfragide, ÁLivres)
41XD09
42YD09
43ZD08
44
45
46
47
48
49
50
51
52
53
54
55
PLANNING
Cells with Data Validation
CellAllowCriteria
P3List=$S$3:$S$6
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
Solution
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
ABCDEFGHIJKLMNOPQ
1
2
3AD09299 Lx - Amadora (MÁgua - Sul)300 Lx - Amadora (MÁgua - Norte)
4BD07
5CD07
6DD05FD08
7ED02GD05
8HD05
9ID03
10JD02
11
12
13
14FD08300 Lx - Amadora (MÁgua - Norte)
15GD05
16HD05
17ID03
18JD02
19
20
21
22
23
24
25KD09301 Lx - Amadora (Venteira)
26LD09
27MD09
28ND07
29OD07
30PD06
31QD04
32RD04
33SD04
34TD04
35UD02
36
37
38
39
40VD10302 Lx - Amadora (Alfragide, ÁLivres)
41XD09
42YD09
43ZD08
Sheet1
Cell Formulas
RangeFormula
P6:Q10P6=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.

Excel Formula:
=INDEX(B:C,SEQUENCE(MATCH(TRUE,INDEX(B:B,MATCH($P3,$F:$F,0)):B1000="",0)-1,,MATCH($P3,$F:$F,0)),{1,2})
 
Last edited:
Upvote 0
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
ABCDEFGHIJKLMNOPQ
1
2
3AD09299 Lx - Amadora (MÁgua - Sul)300 Lx - Amadora (MÁgua - Norte)
4BD07
5CD07
6DD05FD08
7ED02GD05
8HD05
9ID03
10JD02
11
12
13
14FD08300 Lx - Amadora (MÁgua - Norte)
15GD05
16HD05
17ID03
18JD02
19
20
21
22
23
24
25KD09301 Lx - Amadora (Venteira)
26LD09
27MD09
28ND07
29OD07
30PD06
31QD04
32RD04
33SD04
34TD04
35UD02
36
37
38
39
40VD10302 Lx - Amadora (Alfragide, ÁLivres)
41XD09
42YD09
43ZD08
Sheet1
Cell Formulas
RangeFormula
P6:Q10P6=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.

Excel Formula:
=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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top