INDEX MATCH range across a row?


Wise People in my Computer,

Can I set the range for an INDEX MATCH formula across multiple columns in one row, instead of a column? My testing is suggesting the answer is I missing something, else what might I use instead?

I am trying to return the name of a Reviewer into my Lead Reviewer column, pulling from the names in Contact type columns Reviewer 1, Reviewer 2, Reviewer 3. I have created a hierarchy as a helper and want to return the name of whomever has the hierarchy number closest to 1 (from columns Reviewer Hierarchy 1, Reviewer Hierarchy 2, Reviewer Hierarchy 3).

I’ve tried using an INDEX MATCH MIN formula. It works if I only reference one column for each parameter. If I try the below I get an INVALID VALUE error.

=INDEX([Reviewer 1]@row:[Reviewer 3]@row, MATCH(MIN([Reviewer 1 Hierarchy]@row:[Reviewer 3 Hierarchy]@row), [Reviewer 1 Hierarchy]@row:[Reviewer 3 Hierarchy]@row, 0))


Best Answer


  • Hannah H
    Hannah H ✭✭✭✭✭

    Thank you @David Joyeuse that worked perfectly. I was so close, with just the 1 in the INDEX missing. Can you tell me, what does the 1 do?



  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    edited 10/20/20

    @Hannah H

    Well, the 1 here refers to the line number. That part of the INDEX function is mandatory to not mistake Line and Column numbers.

    So since you're going with a range of 1 Line and X columns, you need the 1 to tell INDEX that's it's on Line 1 :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!