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

Screen Shot 2020-10-19 at 3.02.26 PM.png

Find more posts tagged with

Formulas

Accepted answers

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!

All comments

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

Eden B

Hi @David Joyeuse, is there a possibility that you can provide an example because this isn't working for me

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.