lookup value

[Abdulkhadar]

Hi Experts,
I want the data of the row in column wise as per when select a number from dropdown list at K2,

Thanks in adavance.

Dropdown.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		NA0006	019-NA	020-NA	021-NA	022-NA	954-NA	955-NA	139-NA		NA0006
3		NA0017	024-NA	025-NA	089-NA	094-NA	954-NA	955-NA			019-NA
4		NA0026	111-NA	112-NA	127-NA	139-NA	954-NA	955-NA			020-NA
5		NA0028	112-NA	127-NA	139-NA	954-NA	955-NA				021-NA
6		NA0047	089-NA	094-NA	954-NA	955-NA					022-NA
7		NA0048	020-NA	021-NA	022-NA	023-NA	024-NA	954-NA	955-NA
8
9
Sheet1

Cell Formulas
Range	Formula
K3	K3	=C2
K4	K4	=D2
K5	K5	=E2
K6	K6	=F2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2	Cell Value	duplicates	text	NO
K3:K6	Cell Value	duplicates	text	NO
K2	Cell Value	duplicates	text	NO
G7	Cell Value	duplicates	text	NO
H7:I7	Cell Value	duplicates	text	NO
E7:F7	Cell Value	duplicates	text	NO
C7:D7	Cell Value	duplicates	text	NO
E6:F6	Cell Value	duplicates	text	NO
C6:D6	Cell Value	duplicates	text	NO
G3:H3	Cell Value	duplicates	text	NO
G2:H2	Cell Value	duplicates	text	NO
C5:G5	Cell Value	duplicates	text	NO
C4:H4	Cell Value	duplicates	text	NO
C3:F3	Cell Value	duplicates	text	NO
C2:F2	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
K2	List	=$B$2:$B$7

 

[SanjayGMusafir]

Check this and revert - Look at Column L
Have to use Underscore as I don't know the data source and Named range won't allow to use Same Name...
Another concern is - Will it spill in Excel 2019...

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		NA0006	019-NA	020-NA	021-NA	022-NA	954-NA	955-NA	139-NA		NA0006	NA_0006
3		NA0017	024-NA	025-NA	089-NA	094-NA	954-NA	955-NA			019-NA	019-NA
4		NA0026	111-NA	112-NA	127-NA	139-NA	954-NA	955-NA			020-NA	020-NA
5		NA0028	112-NA	127-NA	139-NA	954-NA	955-NA				021-NA	021-NA
6		NA0047	089-NA	094-NA	954-NA	955-NA					022-NA	022-NA
7		NA0048	020-NA	021-NA	022-NA	023-NA	024-NA	954-NA	955-NA			954-NA
8												955-NA
9												139-NA
10
Sheet1

Cell Formulas
Range	Formula
L3:L9	L3	=TRANSPOSE(INDIRECT(L2))
K3	K3	=C2
K4	K4	=D2
K5	K5	=E2
K6	K6	=F2
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
NA_0006	=Sheet1!$C$2:$I$2	K3

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		NA0006	019-NA	020-NA	021-NA	022-NA	954-NA	955-NA	139-NA		NA0006
3		NA0017	024-NA	025-NA	089-NA	094-NA	954-NA	955-NA			019-NA
4		NA0026	111-NA	112-NA	127-NA	139-NA	954-NA	955-NA			020-NA
5		NA0028	112-NA	127-NA	139-NA	954-NA	955-NA				021-NA
6		NA0047	089-NA	094-NA	954-NA	955-NA					022-NA
7		NA0048	020-NA	021-NA	022-NA	023-NA	024-NA	954-NA	955-NA		954-NA
8											955-NA
9											139-NA
10
Sheet5

Cell Formulas
Range	Formula
K3:K9	K3	=INDEX($C$2:$I$7&"",MATCH($K$2,$B$2:$B$7,0),ROWS(K$3:K3))

 

[Abdulkhadar]

How about

Thanks once again. it works

 

[Fluff]

Glad we could help & thanks for the feedback.