CHOOSE Function (Automatic) - Automatic Choose Array

Hello Excel Experts,

I have a question for you! Since you all are experts, I believe you guys are familiar with the CHOOSE function.

=CHOOSE(Index_Num, Value1, [Value2],...)

Normally, I would use CHOOSE function as the following i.e. =choose($A$1, "US", "Canada", "Europe"), where when the value of $A$1 = 1, it'll be US, 2, be Canada etc.

What I'm trying to accomplish here is SIMILAR to the following formula WITH THE EXCEPTION of listing all of the country.

Therefore, ASSUMING if I have all the country's name across row 1, is there a way to make the Value1, [Value2], [Value3] etc to be "automatic" referencing to the rows?

i.e. =choose($A$1, row($1:$1)), where IF the traditional way of doing would be =choose($A$1, "US", "Canada", "Europe", "Australia", "Japan", "Korea") assuming if there's only 6 countries listed in row 1 in column B, C, D, E, F, G respectively for US, Canada, Europe, Australia, Japan and Korea.

Thanks for replying Norie and FDibbins!

To be honest, I'm not an excel expert. Would you please enlighten me how to use CHOOSE and Index together?

Reason I'm using CHOOSE formula is because I'll be "link"ing it to an option button

You are definitely right ChemistB...I have my option button linked to a cell, which in this example is A1.

Norie, not that I don't want to attach an example, but I haven't even accomplish the initial step where I originally thought of using CHOOSE function to automatically update the Value arrays.

Thank you for all the contribution. I shall increase your (ChemistB, Norie & FDibbins) reputation...Please check!

Hi All,

Sorry for the trouble! I came back with a question again.

As you guys have know I'll be linking a cell to an option button. Option button would give me a value of 1, 2, 3...etc. Also, I realized that I have the country to be in a merged...US in column C-E, Canada column F-H etc...always 3 columns merged.

Is there a way to use Index/Match where when the value of A1 is 1, it'll be US, and 2 will be Canada etc.

It was indeed the same formula...variable multiply 3 less 2. Thanks Norie!!!