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
Best Answer

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.
Answers

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.

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.

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

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:

Thank you very much :) x 10
Help Article Resources
Categories
Check out the Formula Handbook template!