Formula Help... Vlookup, Index, Match, Collect?

I was hoping to get some help building a formula to accomplish looking at 3 different variables and then matching that up on a table to give the solution.

I want to have a formula that populates the "plant ID number" when it looks up the "plant", "mix type", and "RAP Content". I have these listed in a table on a separate sheet for it to reference (second picture). So for instance the first row in the first picture the plant ID Number should be 241453 based on the fact the plant is "coffee lake" the mix type is "lvl 3 1/2" ODOT" and the RAP content is "30%".

Hope this all makes sense.


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @JakeMote

    I suggest you add a helper column to BOTH sheets where you JOIN the three pieces of data. The data must be joined in the same order so that the resulting text strings will be a match.

    To Join the three columns you just use a + between the fields. Do this on each sheet in the helper column

    =Location@row+[Mix Description]@row+[Rap Content]@row

    If you wanted a separator in between each data piece, it would be (assuming a hyphen)

    =Location@row+"-"+[Mix Description]@row+"-"+[Rap Content]@row


    After creating these Joined helper columns, this formula will go inside your sheet2 Plant ID Number field.

    =INDEX({sheet1 Mix ID column}, MATCH([sheet2 Joined Helper column]@row, {sheet1 Joined Helper column},0))

    Will this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @JakeMote

    I suggest you add a helper column to BOTH sheets where you JOIN the three pieces of data. The data must be joined in the same order so that the resulting text strings will be a match.

    To Join the three columns you just use a + between the fields. Do this on each sheet in the helper column

    =Location@row+[Mix Description]@row+[Rap Content]@row

    If you wanted a separator in between each data piece, it would be (assuming a hyphen)

    =Location@row+"-"+[Mix Description]@row+"-"+[Rap Content]@row


    After creating these Joined helper columns, this formula will go inside your sheet2 Plant ID Number field.

    =INDEX({sheet1 Mix ID column}, MATCH([sheet2 Joined Helper column]@row, {sheet1 Joined Helper column},0))

    Will this work for you?

    Kelly

  • Kelly,


    This worked well thank you for the advise!

    Thanks

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!