Index Match Help

Mandy M

Hi! any help on why this formula is not working?

=INDEX({Operations BOB LON H}, MATCH({Operations BOB Range 4}@row, {Operations BOB Project Code}, 0))

this is a similar formula that we have and it is working:

=INDEX({Ref. BOB Range 1}, MATCH([Project Code]@row, {Ref Project Code}, 0))

they appear the same to me, what am I Missing?

Janae G.

Hello Mandy!

It looks like in the first formula, you are referencing another sheet for the range portion (first part) of the MATCH function, which is indicated by the curly brackets { }. In the bottom formula you can see that the range for MATCH is referencing the current sheet, indicated by square brackets [ ]. So {Operations BOB Range 4}@row is the issue: Smartsheet can't find that range in the current sheet, and wouldn't know what is meant by @row in a different sheet either.

For the MATCH function, the first thing you write is the range for the index in the current sheet that you are searching for in the other sheet, something like a unit number or employee number, or like Project Code as you had in the second formula.

Here are some quick links I found helpful on Index and Match functions:

INDEX Function | MATCH Function | Index and Match — Smartsheet Community

Hope this helps, and good luck!

Mandy M

hey thanks!

even when changing the bracket style - it still shows me the same error :-(

Paul Newcome

That first part of the MATCH function should be a refence to a column that is in the same sheet as the formula. Basically the string you are wanting to search for in the second sheet.