[Solved] Return cell range based on column header

[RickMcc]

Hi again,

Newbie here. I have a dropdown list on page 1 with 10 variables the user can pick from. Once the user selects an option, I then need to return the value of 10 cells in a column from an array on page 2. Is there a better way of doing this then using a large if/then statement? I am not looking for someone to write the formula for me, but to be pointed in the direction for best way to get the outcome I am looking for, and then I can research it. Attached is a test sheet I am using for practice. Thank you for your help.

[MrProgrammer]

I then need to return the value of 10 cells in a column from an array on page 2

The INDEX and MATCH functions can do that. A single array formula can fill D5:D14.
Using array formulas

202412121236.ods

(11.18 KiB) Downloaded 88 times

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[RickMcc]

Thank you very much! I really do appreciate your help.

[RoryOF]

If one is working with listboxes, there is a set of prewritten macros for these in OpenOffice at /Tools /Macros /Organise Macros OpenOffice Macros /Tools /Listbox

[RickMcc]

OK, as I understand it. The index in this instance is area I'm searching to find the info I am looking for, and the Match finds and displays the corresponding data that matches the value from the value entered into the search cell.

My search cell is E1 on page 1. My array is on page 3 E2:P17, and I am Matching data from sheet3 E2:P2. Here is my formula
=INDEX($sheet3,$E$2:$P$17;0;MATCH(E1;sheet3,$E$2:$P$2;0))

I am getting the Err 508, but I do not see the comma vs semi colon error. What am I over looking??

[Alex1]

The sheet name must be followed by a period instead of a comma.
For the dropdown list in A1, instead of a list you can select the cell range $Sheet2.$A$1:$J$1.

[RickMcc]

I'm sorry to pester y'all again about this, but I have the statement written as follows:

=INDEX($Sheet3.$E$2:$P$11;0;MATCH(E1;$Sheet3.$E$1:$P$1;0))

However it pulls the data stating at cell E6 on sheet 3 instead of E2. Everything I have researched, which granted has only been 1 day's worth, is telling me I have the formula written correctly. Shouldn't the ";0;" mean it should start pulling the dats from the first cell which in this case is E2 on sheet 3?

[Alex1]

It's hard to tell without knowing what's in the table. Did you enter the formula as an array formula?

[MrProgrammer]

Shouldn't the ";0;" mean it should start pulling the dats from the first cell which in this case is E2 on sheet 3?

Read the documentation for the INDEX function to learn what 0 means.

=INDEX($Sheet3.$E$2:$P$11;0;MATCH(E1;$Sheet3.$E$1:$P$1;0))

These ranges don't match Test.ods or 202412121236.ods so we have no idea what the problem might be. The quickest way to determine why the formula doesn't do what you expect is to attach your data and to state what result is desired. This is an array formula. Read the link I gave you to learn about entering array formulas.

[RickMcc]

It's hard to tell without knowing what's in the table. Did you enter the formula as an array formula?

Forgot to attach the spreadsheet earlier. Full disclosure, I'm RPG nerd and this a character sheet I am trying to get working.

[Alex1]

Click E6 on Page 1, press F2, type a space, then backspace, then Ctrl+Shift+Enter.

[RickMcc]

I got it working. This is how updated the formula

=INDEX('Sheet3'.$E$2:$P$11;ROW(A1);MATCH($E$1; 'Sheet3'.$E$1:$P$1;0))