INDEX MATCH range across a row?

Hannah H

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 no...am 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))

David Joyeuse

Hi @Hannah H

It is possible to use INDEX/MATCH on columns.

Index is constructed like this:

INDEX([Value to return], [Line N# of the Value to return], [Column N# of the Value to return]).

The Column N# is optional within Index, but you can definately write your MATCH formula in this part so it will look like:

=INDEX([Value to return], 1, MATCH([Value to search], {Columns range},0))

Hope it helped!

David Joyeuse

Hi @Hannah H

It is possible to use INDEX/MATCH on columns.

Index is constructed like this:

INDEX([Value to return], [Line N# of the Value to return], [Column N# of the Value to return]).

The Column N# is optional within Index, but you can definately write your MATCH formula in this part so it will look like:

=INDEX([Value to return], 1, MATCH([Value to search], {Columns range},0))

Hope it helped!

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?

Thanks,

Hannah

David Joyeuse

@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 :)