I am attempting to populate a cell based on information mastered in another table.

Options

Sheet 1 contains a list of 83 values in a column (Alignment Look up L3) that each correspond to a value in the previous column (Alignment Look up L2).

In Sheet 2, I have to select from the list of 83 values [Business Process (L3)]@row, thus want the previous column to autopopulate so I do not need to look it up each time.

For example, on Sheet 1, Customer Billing corresponds to Revenue Management and Account Planning corresponds to Lead Generation. Thus, on Sheet 2, when I select Customer Billing from a dropdown, I want "Revenue Management" to autopopulate and when I select Account Planning, I want "Lead Generation" to populate. I've tried IF, VLOOKUP with no sucess.

I'm now trying: =INDEX({Alignment Lookup L2}, MATCH("[Business Process (L3)]@row", {Alignment Lookup L3}))

This finally returns a value, but it doesn't match...

Any suggestions on what I'm doing wrong?

Best Answer

  • Heather Mize
    Heather Mize ✭✭✭✭
    Answer ✓
    Options

    Your formula was very close! This helped to see how it needed to be laid out:

    =INDEX({Column with value to return}, MATCH("Value to match", {Column with value to match in other sheet}, 0))

    So you just needed to remove the quotes around the Business Process L3 @row, and add the 0 after the brackets before closing parenthesis.

    =INDEX({Alignment Lookup L2}, MATCH([Business Process (L3)]@row, {Alignment Lookup L3}, 0))


    Thank you!

    Heather

Answers

  • Heather Mize
    Heather Mize ✭✭✭✭
    Answer ✓
    Options

    Your formula was very close! This helped to see how it needed to be laid out:

    =INDEX({Column with value to return}, MATCH("Value to match", {Column with value to match in other sheet}, 0))

    So you just needed to remove the quotes around the Business Process L3 @row, and add the 0 after the brackets before closing parenthesis.

    =INDEX({Alignment Lookup L2}, MATCH([Business Process (L3)]@row, {Alignment Lookup L3}, 0))


    Thank you!

    Heather

  • Aileen
    Aileen ✭✭
    Options

    Really appreciate this - it worked. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!