Return a range by looking up one cell.

[Tempest_Petals]

Hi! I'm working on a damage simulator/calculator for a game and I've run into a bit of a snag.

I have a bunch of character data setup in little tables (not an actual "Excel Table") and what I'm trying to do is have the user select a character by name, and then have the sheet return the whole range. 

For example, if a user chooses "A Knight" on the main sheet, I'd like to be able to look him up on this data sheet, and display all of his info back on the main sheet.

I've tried variations on XLOOKUP, Filter, and Index but haven't met with any success.

If anyone has a solution, or an idea I could try, I would be most grateful.

This how my data is currently structured.

Thank you for your time!

[#2 · Accepted solution]

@Tempest_Petals 

Have you tried naming the various ranges of data, so that the entire range can be displayed? I don't know for sure that it'll work, but it occurs to me; at least it's something you've not named so far.

[#3]

@mathetes 

Hmmm, that does indeed work, but I'm not sure how I'd implement it.

I've run into this type of issue before. I need some way to access a list of those named ranges.

If I could put them in a table or list and use a lookup function that'd be great but I wasn't able to find a way to do that. They have to be inserted into a formula, so my workaround was to make a long IFS formula.

Unfortunately that wont work here, the list is much too long.

Would you happen to know a way of to accomplish this?

[#4]

@Tempest_Petals 

Would you happen to know a way of to accomplish this?

You need to be a bit more descriptive of what the "this" is. You've talked of needing to access a list of the names. You've talked of using the names in a formula. I can envision a dropdown (data validation) as a way to access a list. Also one of many lookup-type functions. But then you include the notion of including the names (presumably one at a time) in a formula, but you don't say what it is that the formula is supposed to do.

Talk us through the full process (describing it in English, not in "Excelese"; let us worry about the translation into Excel functions or procedures) ...  This would probably be easy if we were sitting down face-to-face, because we could go back and forth, clarifying as we go along. Instead, I'm asking you to describe pretty thoroughly what the whole process is here. It might help if you assume:

1. you're talking with a relatively intelligent person
2. that person knows nothing of gaming, game characters and what one does with them
3. that person does know Excel, but wants you to not talk Excel per se, just to talk of the information and what needs to happen to it, with it, etc.

[#5]

@mathetes 

My apologies for being unclear. I will try to do a better job.

If I were to use your idea of making named ranges for each character, I would then have 40+ named ranges.

What I'm not sure of, is how I would access/reference them; so one could be used in a function to call up the associated data.

As a crude example, if a user chose Centurion from this list, I would then need a way to pull up Centurion's named range so the data could be displayed.

I hope that makes more sense. I really appreciate your time.

[#6]

@mathetes 

I believe I found a solution! 
I made a new table with the character names and their respective named ranges.
Then used xlookup wrapped in indirect.
So the final formula looks like this:

=INDIRECT(XLOOKUP(A1, CharData[Characters], CharData[Named Range]))

I'm very grateful for you help. You definitely got me on the right path.
Thank you!

[#7]

That's good to hear. Glad I could play a part.