Trying to populate a list from another sheet by a single reference number

Question

I am working on a handy reference sheet for my DnD Games, that includes 10 pre-planned encounters of varying difficulty that I want to reference on an Initiative Sheet by referencing the Encounter Number (1-10), but am having difficulty with generating the entire list of monsters. Here are some reference screenshots to what I am trying to accomplish:

Initiative Tracker

This is the Initiative Tracker (Sheet "Initiative" that I have and am looking to populate by the reference number at the top (So inputting "1" would return a list of monsters from the Encounter Generator listed as encounter "1")

Encounter Generator

This is the table (Sheet "ENCOUNTER PROTO") that I want to be able to populate in the table, the Encounter Number is on the side and I want that to be the reference to pull the monsters off of this list

Ideally what would happen is that depending on the actions of my players I could take a pre-generated battle list of monsters and populate that list again so that I can track and list initiative using another set of equations and then sort them automatically to track order of attack. I've tried XLOOKUP and INDEX equations:

=XLOOKUP($G$2,'ENCOUNTER PROTO'!$B$6:$B$105,'ENCOUNTER PROTO'!$D$6:$D$105,"",0,1)

[This equation replicates the same monster listed on line 1 and doesn't change over to the next one]

and

=INDEX('ENCOUNTER PROTO'!$D$6:$D$105,SMALL(IF(Initiative!$G$2='ENCOUNTER PROTO'!$B$6:$B$105,ROW('ENCOUNTER PROTO'!$D$6:$D$105)-5,""),ROW()-4))

[This one returns the monster listed on line 1 then an error message of #NUM! on all subsequent lines]

Not sure I understand it correctly, but would this be in the direction of what you mean? =TAKE(XLOOKUP($G$2,'ENCOUNTER PROTO'!$B$6:$B$105,'ENCOUNTER PROTO'!$D$6:$D$105,"",0,1):$D$105,10) — P.b
– P.b, Commented Nov 20 at 20:56
I had to upgrade to Office 365 to use TAKE but the equation should work in theory, it tests well in grabbing the first item in each encounter section, but it is not grabbing 10 rows... =TAKE(XLOOKUP($G$2,'ENCOUNTER PROTO'!$B$6:$B$105,'ENCOUNTER PROTO'!$D$6:$D$105,"",0,1),10) Stranger yet, when the G2 field is left blank it moved to the second item in the 1st encounter which is unexpected. — Jonathan Rogers
– Jonathan Rogers, Commented Nov 21 at 0:26
What P.b means is =TAKE(XLOOKUP($G$2,'ENCOUNTER PROTO'!$B$6:$B$105,'ENCOUNTER PROTO'!$D$6:$D$105,"",0,1):'ENCOUNTER PROTO'!$D$105,10). Similarly, you could return multiple (three) consecutive columns with, e.g., =IFNA(TAKE(XLOOKUP($G$2,'ENCOUNTER PROTO'!$B$6:$B$105,'ENCOUNTER PROTO'!$D$6:$F$105):'ENCOUNTER PROTO'!$F$105,10),""). — VBasic2008
– VBasic2008, Commented Nov 21 at 1:45
Don't use a merged cell for the Encounter number. It should be on every line. Yeah, it looks cool, but it makes things far less functional. With a separate Ecounter # on each line, you can use Filter to extract the data you want. — Frank Ball
– Frank Ball, Commented Nov 21 at 14:18
@VBasic2008 thanks gor correcting. That was indeed what I ment. I forgot yo include the Sheet name. — P.b
– P.b, Commented Nov 21 at 18:36

VBasic2008 · Accepted Answer · 2025-11-21 02:30:29Z

0

Lookup Data

In G5:

=LET(lookup_value,G2,sdata,'ENCOUNTER PROTO'!B6:J105,sl_col,1,sr_cols,{2,3,4,6},
    IFNA(CHOOSEROWS(CHOOSECOLS(sdata,sr_cols),
        SEQUENCE(10,,XMATCH(lookup_value,INDEX(sdata,,sl_col)))),""))

If you don't need column G, then use sr_cols,{3,4,6}, in H5.

edited Nov 21 at 2:30

answered Nov 21 at 2:25

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trying to populate a list from another sheet by a single reference number

1 Answer 1

Lookup Data

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Lookup Data

Comments

Your Answer

Sign up or log in

Post as a guest

Related