Cannot get Index Match to work with 2 matches and a specific criteria

[morerockin]

Hello All,

See attached. In Sheet3, Column BC, I am trying to input a formula that will return the value in Sheet2 column J based on the following match:

Sheet3 column K matches Sheet2 column F
Sheet3 column CD matches Sheet2 column B

After making those matches, the formula has to find the string "CONLAB" in Sheet2 column G. After all of this logic, the data from Sheet 2 column J should now appear in Sheet3 column BC.

Does this make sense

[JBeaucaire]

The normal INDEX/MATCH syntax to one range, the inner MATCH() range must be replaced with an INNER INDEX() that merges the two search columns into a single searchable text string. Like so:

=INDEX(Sheet2!$J$1:$J$1000, MATCH($K2 & "-" & $CD2, INDEX(Sheet2!$F$1:$F$1000 & "-" & Sheet2!$B$1:$B$1000, 0), 0))

This is for 1000 rows of data, only expand that as far as you actually need.

[morerockin]

The normal INDEX/MATCH syntax to one range, the inner MATCH() range must be replaced with an INNER INDEX() that merges the two search columns into a single searchable text string. Like so:

=INDEX(Sheet2!$J$1:$J$1000, MATCH($K2 & "-" & $CD2, INDEX(Sheet2!$F$1:$F$1000 & "-" & Sheet2!$B$1:$B$1000, 0), 0))

This is for 1000 rows of data, only expand that as far as you actually need.

Thank you. I think I've got that part. Now I am struggling to get the formula to only return a value if it finds the string "CONLAB" in column G of sheet 2.

[JBeaucaire]

You expand from INDEX of two columns to index of 3.

=INDEX(Sheet2!$J$1:$J$1000, MATCH($K3 & "-" & $CD3 & "-CONLAB", INDEX(Sheet2!$F$1:$F$1000 & "-" & Sheet2!$B$1:$B$1000 & "-" & Sheet2!$G$1:$G$1000, 0), 0))

[morerockin]

You expand from INDEX of two columns to index of 3.

=INDEX(Sheet2!$J$1:$J$1000, MATCH($K3 & "-" & $CD3 & "-CONLAB", INDEX(Sheet2!$F$1:$F$1000 & "-" & Sheet2!$B$1:$B$1000 & "-" & Sheet2!$G$1:$G$1000, 0), 0))

Thank you. I am going to practice this! SOLVED!

[morerockin]

Hello All,

See attached. See Sheet 3. Column BC has a formula that searches Sheet2 and returns data.

I've added two more caveats. I need the formula to not only search for "CONLAB". I need it to search for "CONLAB" "FULLSC" and "INST". If it finds one of these three for the corresponding site ID, it should return that PID in Sheet2 Column G. If it finds one or more like on sheet2 rows 8 and 9, it should return the word "duplicate".

Let me know if that makes sense.

[morerockin]

BumpageBumpage

[FDibbins]

Once you start getting that many criteria, it might be better to add a helper column to test for all of those, and based on that test, either create a unique reference number (which would eliminate the need for an array formula) or return TRUE or FALSE, then use that in Jerry's suggestion.

I used column K for the helper...
K2=IF(OR(G2="CONLAB",G2="FULLSC",G2="INST"),K1+1,K1)
copied down

Then for the extract...
=IFERROR(INDEX(Sheet2!$J$1:$J$1000,MATCH(ROW(1:1),Sheet2!$K$1:$K$1000,0)),"")

[morerockin]

Once you start getting that many criteria, it might be better to add a helper column to test for all of those, and based on that test, either create a unique reference number (which would eliminate the need for an array formula) or return TRUE or FALSE, then use that in Jerry's suggestion.

I used column K for the helper...
K2=IF(OR(G2="CONLAB",G2="FULLSC",G2="INST"),K1+1,K1)
copied down

Then for the extract...
=IFERROR(INDEX(Sheet2!$J$1:$J$1000,MATCH(ROW(1:1),Sheet2!$K$1:$K$1000,0)),"")

Sorry FDibbins. It is hard to understand my directions.

So for site # 95217373, it should have returned the word "Duplicate" in cell BC7 because the site has two PID's listed.

Also, it doesn't appear that the formula you gave with the helper column is returning the right R in column J of sheet2.