INDEX and MATCH to complete a Two Column Look-up

I have used excel to complete two column look-ups before using this formula:


J5=column 1 item to match

D5=column 2 item to match

E32:E41= column 1 array to look-up

B32:B41=column 2 array to look-up

However, Smartsheet does not seem to recognize the "&" ampersand function in the formula.

What is a workaround for this missing feature?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you provide a screenshot of what you are trying to accomplish with mock data manually entered?

  • Here is the original Excel document i was using. The two column look-up function formula works.

    I select the "Week" and the "STORE" and it returns the value in the "Labour" column.

    Here is the excel formula I used: =INDEX(P32:P41,MATCH(J5&D5,E32:E41&B32:B41,0))

    Here are my smartsheets. The master data is in one sheet, and the table is in another.

    Here is the smartsheet formula that is the same as excel, but does not work:

    =INDEX({SUN Labour},match([C2]1&[C5]1,{Date}&{Store},0))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. You are going to want to use an INDEX/COLLECT instead.

    =INDEX(COLLECT({Labor Range}, {Date Range}, [C2]1, {Store Range}, [C5]1))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!