Index/Match

[mikehay08]

I am trying to setup a jeopardy game. The questions and answers are in one file and I'd like those inserted into a template I have. The questions and answers are not always in the same cell across a row, so I am try to do a index match formula in the template to find the proper input, but my mind just isn't working today.
I manually input category 1 in cell A4 of template. Cell A9 should find value in A4 on the input sheet, and then enter next category (ie the value 3 cells over), and so on for each category. Cell C4 of template should find proper category and subsequent money valued question (ie $200, $400, or $600) based on formula.

[mikehay08]

I have been working to no avail. I got the question part on the show# input sheet (attached), but when new information is loaded into the question Answer template, the cells do not match exactly, so my formulae aren't working. I need to be able to do multiple index/matches for one column (ie look for category 1 AND then look for amount (ie $200, $300 etc.)) and return the cell that is just below the category amount. (that cell reference is always the same it seems)

[Estevaoba]

Your data organization does not follow a row pattern, so it is difficult to import them as desired.
I copied your Template to the Question & Answers file to make this job easier.
I organized the questions so they are in the same row across all category columns.
Then I use this formula in helper column T in the Questions tab, to index the rows with questions, in T15 copied down to T23:

Please Login or Register  to view this content.

I could not do this in the Answers tab, because, as mentioned above, the answers across categories are not listed in the same row. I just manually entered Q1 for the first answer row.

Also, I changed columns C & D headers in Template tab, to match respective tabs (Questions & Answers), to make use of the INDIRECT function in this formula in C4, copied across and down:

Please Login or Register  to view this content.

Finally, to list categories from cell A9 I use this formula in A9 and copied down. This formula is based on the 5-row increment as it is copied down the merged cells:

Please Login or Register  to view this content.

This is just an idea to help you get started.
Hopefully, after you manage to organize your data, it will guide you through to get the job completed.
Please check file attached.

Good luck!

[mikehay08]

Wow, I am just in awe really that people like doing excel so much they're willing to help me with my dumb game! I am a step above noice, but certainly not a pro at excel, so after studying your work, I am not in full understanding, however I think if I spend more time with it, I will get there.

My issue is that the information is copy and pasted from a website. I plan on doing bulk copy and pastes of jeopardy games and was trying to avoid having to make edits once pasted. It may be that that is indeed not possible because as you said, that information is not consistent across rows.

Thank you again kind stranger, I really do appreciate the help!

[Estevaoba]

You're welcome. Glad to help.
Thank you for the feedback and the reputation points added.