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??.


[TABLE="width: 684"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Bearing[/TD]
[TD]Bearing 1[/TD]
[TD]Bearing 2[/TD]
[TD]Bearing 3[/TD]
[TD]Bearing 4[/TD]
[TD]Bearing 5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Roll[/TD]
[TD]Roll 1[/TD]
[TD]Roll 2[/TD]
[TD]Roll 3[/TD]
[TD]Roll 4[/TD]
[TD]Roll 5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Rice[/TD]
[TD]Rice 1[/TD]
[TD]Rice 2[/TD]
[TD]Rice 3[/TD]
[TD]Rice 4[/TD]
[TD]Rice 5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Bearing[/TD]
[TD][/TD]
[TD]Bearing 1[/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"] =CHOOSE(COLUMNS($E8:E8),INDIRECT($C8))[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Roll[/TD]
[TD][/TD]
[TD]Roll 1[/TD]
[TD]Roll 2[/TD]
[TD]Roll 3[/TD]
[TD]Roll 4[/TD]
[TD]Roll 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=IFERROR(VLOOKUP($C$11,$D$3:$I$5,COLUMNS($E11:E11)+1,),"")[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.