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

Thanks

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

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