If number in cell matches any in a column then show value from adjacent cell

[PuntingJawa]

I am trying to automate a filter based on a numbered dropdown menu.
Example:
If I have the number "1" in cell A3, I want to search column B for that number and return with the value from the adjacent in column C to column D without skipping spaces. (Minisheet below of what I am trying to make it look like).

Shipping PSI verify prints - serial label print.xlsx
	A	B	C	D
2	Number	DATA	VALUE	RETURN
3	1	1	A	A
4		2	B	D
5		3	C
6		1	D
7		2	E
8		3	F
Sheet14

 

[awoohaw]

Try:

Mr Excel Questions 71.xlsm
	A	B	C	D
1	Number	DATA	VALUE	RETURN
2	1	1	A	A
3		2	B	D
4		3	C
5		1	D
6		2	E
7		3	F
PuntingJawa

Cell Formulas
Range	Formula
D2:D3	D2	=DROP(FILTER(B2:C7,A2=B2:B7),,1)
Dynamic array formulas.

 

[PuntingJawa]

Try:

Mr Excel Questions 71.xlsm
	A	B	C	D
1	Number	DATA	VALUE	RETURN
2	1	1	A	A
3		2	B	D
4		3	C
5		1	D
6		2	E
7		3	F
PuntingJawa

Cell Formulas
Range	Formula
D2:D3	D2	=DROP(FILTER(B2:C7,A2=B2:B7),,1)
Dynamic array formulas.

I've tried expanding this. I get the #CALC! error. What I posted was an example but I need to go up to cell 122.

 

[awoohaw]

what do you mean by expanding?

The formula is a spilled array, you cannot put anything in cells below the formula until the SPILL is complete.
The ranges in the formula a relative, and if you didn't have the #SPILL concern you can eliminate the #CALC by making the references absolute.

=DROP(FILTER($B$2:$C$7,A2=$B$2:$B$7),,1)

you can make the array spill to the side by using the TRANSPOSE Function:

=TRANSPOSE(DROP(FILTER($B$2:$C$7,A2=$B$2:$B$7),,1))

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1	Number	DATA	VALUE	RETURN
2	1	1	A	A	D
3	2	2	B	B	E
4	3	3	C	C	F
5		1	D
6		2	E
7		3	F
PuntingJawa

Cell Formulas
Range	Formula
D2:E4	D2	=TRANSPOSE(DROP(FILTER($B$2:$C$7,A2=$B$2:$B$7),,1))
Dynamic array formulas.

 

[PuntingJawa]

what do you mean by expanding?

The formula is a spilled array, you cannot put anything in cells below the formula until the SPILL is complete.
The ranges in the formula a relative, and if you didn't have the #SPILL concern you can eliminate the #CALC by making the references absolute.

=DROP(FILTER($B$2:$C$7,A2=$B$2:$B$7),,1)

you can make the array spill to the side by using the TRANSPOSE Function:

=TRANSPOSE(DROP(FILTER($B$2:$C$7,A2=$B$2:$B$7),,1))

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1	Number	DATA	VALUE	RETURN
2	1	1	A	A	D
3	2	2	B	B	E
4	3	3	C	C	F
5		1	D
6		2	E
7		3	F
PuntingJawa

Cell Formulas
Range	Formula
D2:E4	D2	=TRANSPOSE(DROP(FILTER($B$2:$C$7,A2=$B$2:$B$7),,1))
Dynamic array formulas.

I'm sorry, I am very tired and not explaining well. I figured out the way the drop works with what you provided. Thank you very much for your help!

 

[awoohaw]

My pleasure.

Best Wishes!