INDEX MATCH with two criteria

Is there a way to reference a ROW and a Column and return the data?

So in the primary column, I have project numbers, and in the first row i have pasted column names, so I am looking literally look up the cell (by Row and column) not using Index only, because it requires column numbers, but using match.

=INDEX(COLLECT({Whole sheet}, {Master Pr_Numbr}, Data1, HAS {Master Data _ Row 1} ,[Primary Column]@row,)1)

=INDEX(COLLECT({Whole sheet}, {Master Pr_Numbr, Data1, {Master Data _ Row 1} ,[Primary Column]@row,)1)

I am getting responses back such as no match and unparseable.

=INDEX({Master Data Row 3}, MATCH([Primary Column]@row, {Master Data _ Row 1}, 0)) and this returned an #invalid value (I was trying to set the range as a row)

I also Tried index and Match - but I have no idea how to include 2 criteria in the matching piece.

Please help me.

Thanks

Find more posts tagged with

Accepted answers

kirstie858

Let's say your Reference sheet looks like this:

Scenario 1: Your Destination sheet looks like this, where you have the reference values in column 1, and row 1

Formula: =INDEX({whole_sheet}, MATCH($[Variable 1]@row, {project number}, 0), MATCH([Column2]$1, {headers}, 0))

Scenario 2: If your destination is set up like this, you can actually use column formulas.

The formula in my example is this: =INDEX({whole_sheet}, MATCH([Variable 1]@row, {project number}, 0), MATCH([Variable 2]@row, {headers}, 0))

If this doesn't help your issue, as the previous posted commented, it really helps to be able to see your data structure. Please post pics of your origination and destination sheets.

All comments

Danielle Arteaga

The quick answer to your question is that - yes - it is possible to do an INDEX/COLLECT with multiple criteria. The basic syntax is this:

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

But, I think you're trying to a conditional INDEX/COLLECT - meaning, you want the formula to return a value only if there is some other value in a specified cell. If that's the case, I'd be happy to help, but I'd need to see your column structure to better understand what condition(s) you're trying to use.

kirstie858

Let's say your Reference sheet looks like this:

Scenario 1: Your Destination sheet looks like this, where you have the reference values in column 1, and row 1

Formula: =INDEX({whole_sheet}, MATCH($[Variable 1]@row, {project number}, 0), MATCH([Column2]$1, {headers}, 0))

Scenario 2: If your destination is set up like this, you can actually use column formulas.

The formula in my example is this: =INDEX({whole_sheet}, MATCH([Variable 1]@row, {project number}, 0), MATCH([Variable 2]@row, {headers}, 0))

If this doesn't help your issue, as the previous posted commented, it really helps to be able to see your data structure. Please post pics of your origination and destination sheets.

Nicole V

Thank you very much - One last question - how does one select the headers in the source sheet for the reference {headers}. I can do {whole_sheet}, but headers is trickier

kirstie858

As far as I know, you can't reference headers in Smartsheet like you can in Excel. But you stated, " and in the first row I have pasted column names," in your original post. If you look at my images, you'll see the headers in my example data also appear in row 1. Here's what the reference looks like:

Nicole V

Thank you very much :) x 10

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, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.