INDEX and MATCH to complete a Two Column Look-up

Kerith Boyer

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

=INDEX(P32:P41,MATCH(J5&D5,E32:E41&B32:B41,0))

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

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

Kerith Boyer

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

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

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