VLOOKUP can But CHOOSE & INDIRECT can't?????

[excelindianfanclub]

dear experts,
greetings to all,

Created Data Validation on cellS C8 & C11 using values from range D3:D5.When i select the value from data validation list on Cell C8, the CHOOSE,COLUMNS and INDIRECT functions are giving only one correct value (i used Ctrl+Shift+Enter) and are giving VALUE error when i drag the formula in cell E8 towards my right. But When I select a value from data validation cell C11, the functions VLOOKUP and COLUMNS give me what I want say here when I select "Roll" from the selection list on cell C11, the ranges E11:I11 are filled with Roll 1,Roll 2,....Roll 5 When i drag the formula in Cell E11 towards my right.Please give me explanation Why the error occurs??? and HOW do to rectify this error??.

	C	D	E	F	G	H	I
3		Bearing	Bearing 1	Bearing 2	Bearing 3	Bearing 4	Bearing 5
4		Roll	Roll 1	Roll 2	Roll 3	Roll 4	Roll 5
5		Rice	Rice 1	Rice 2	Rice 3	Rice 4	Rice 5
6
7
8	Bearing		Bearing 1	#VALUE!	#VALUE!	#VALUE!	#VALUE!
9			=CHOOSE(COLUMNS($E8:E8),INDIRECT($C8))
10
11	Roll		Roll 1	Roll 2	Roll 3	Roll 4	Roll 5
			=IFERROR(VLOOKUP($C$11,$D$3:$I$5,COLUMNS($E11:E11)+1,),"")

<tbody>
</tbody>

 

[Ron Coderre]

Using your posted data...
This regular formula begins the list if items for the C8 selection
E8: =INDEX($E$3:$I$5,MATCH($C8,$D$3:$D$5,0),COLUMNS($E:E),1)

Copy that formula across through I8 and into E11:I11

Is that something you can work with?

 

[excelindianfanclub]

Dear Ron,
Thanks for reply.but my question is why i am getting error value while dragging the formula =CHOOSE(COLUMNS($E8:E8),INDIRECT($C8)) in cell E8 towards my right Using CHOOSE & INDIRECT.when i use this formula in cell E8,it works and returns value.but i can't get a clue why the hell error occurs When copying to next cell!!. please give me reason why do CHOOSE and INDIRECT functions give error

 

[shift-del]

Hi

please give me reason why do CHOOSE and INDIRECT functions give error

You gave CHOOSE() only one parameter to choose from. And that parameter is INDIRECT($C8).
If you F9 your formula then you will see that the result looks something like this:
{"Bearing 1"."Bearing 2"."Bearing 3"."Bearing 4"."Bearing 5"}
It's an array. And Excel is just displaying the first item in the cell.
Items from an array can only be looked up via INDEX().
CHOOSE() needs a list of parameters.

 

[excelindianfanclub]

Dear del,
thank u..i understood.