Calc - Index, Match, and Dropdown columns?

[csquared]

Hello,
First time posting, bear with me, please.

I have just learned how to get index and match working together, and now I want to expand on that, but I’m not sure how to do what I want.

I have two drop down menus Species and Age. And I have two tables, Table 1 is Species A, Table 2 is Species B, each table has the same breakdown of Age options.

So, my goal is to select the species and have it pull from the correct table and then get the age data I want. I hope that makes sense.

Here is my file, if that will help explain what I mean. Example Sheet.ods

Thanks for the help in advance,
Nick

[JohnSUN]

Nick, the easiest way is to solve your problem using the OFFSET() function. Just calculate how many rows below the header are the necessary data and display them. Yes, the MATCH () function is well suited for calculating offsets. You will also need the ROWS() and IF() functions.

In addition, I recommend using named ranges - the short name Ages is simpler and clearer than $B$2:$B$13

AgeAndSpecies.ods

[csquared]

Thank you for the help. That is exactly what I was wanting it to do.

I looked at your file and tried to click in the formula bar, just so it would highlight and sort of show me what was happening, but it just pops up that i can’t change part of an array?

Can this be used multiple times in the same formula, for example, if I added more species tables?

At least now I know what tutorials to look up on youtube, lol.

[JohnSUN]

About edit array-formula please see here

Yes, for a larger number of tables, the formula will be similar, but slightly different - the IF() function will need to be complicated, perhaps replaced by another MATCH()