Using Index/Match to fill out document

Options
Coffee
Coffee ✭✭✭
edited 11/07/22 in Formulas and Functions

I am trying to use INDEX/MATCH functions to gather the items and quantities to go onto a printed document.

I’ve gotten as far as this function: =INDEX({InventoryIndex}, MATCH($[IndexKey created with a different function]@row, {column in the index with all the index keys}, 0))


My question: Is there a way for the function to start in a row and skip items with a blank QTY? The next column to be filled would then pick up where the previous left off until all items with a QTY in the row have been placed?


Bonus question: Have I overlooked some obviously easier way to accomplish this goal? I’m 3 months new to this and the index is only about a 25% finished. So it's definitely not too late change tactics but I have no idea if I’m making this overly difficult on myself or if this is a reasonable way to do things. 😅

Best Answer

Answers

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    Hi @Coffee

    Can you send a screenshot of what you try to accomplish?

  • Coffee
    Coffee ✭✭✭
    Options

    This is the (test version of the) sheet that generates the form

    This is the sheet that the index is referring to

    Current formula in each column of the first image is: =INDEX({INDEX}, MATCH($[MATL QTY Key]@row, {INDEXKEY}, 0), 1)

    What I'm trying to figure out is if there's a way to have it skip blank columns on the index and then the next QTY column picks up where the previous left off. So, if QTY 7 has to go all the way to column 22 to get a value, then QTY 8 would start in column 23.

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    Answer ✓
    Options

    You could probably use a nestled if statement combined with multiple index matches, something like that.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!