# Index Match formula with Or Exact True/False output

1. ## Index Match formula with Or Exact True/False output

I am sure I'm not doing this right, so any advice on handling this bit would be great.

I am working with some manufacturing facilities and am trying to make sure they are using the correct part numbers moving forward when they update their lines with new products.

I have a common Index Match formula I use to input the updated information on an excel spreadsheet:

=INDEX(Sheet2!\$E\$2:\$E\$10000,MATCH(1,(Sheet2!\$B\$2:\$B\$10000=A2)*(Sheet2!\$C\$2:\$C\$10000=C2)*(Sheet2!\$D\$2:\$D\$10000=B2),0))

This formula is within D2, D3 and so on as each product is checked for each individual piece of information. This allows for me to pull the new part numbers correctly from production to our project management side.

The problem I am running into is on the tail end. I am trying to make sure our project management records show what is actually in production records based upon this same formula. I am trying to work out the formula to either populate a false or true statement based upon matching multiple criteria.

I have used a Exact formula to make a true/false output previously in older versions:

=OR(EXACT(D2,Sheet2!\$E\$2:\$E\$10000))

My thought process was to try and combine these formulas. Use the match formula to check for multiple criteria while placing in the Or Exact formula to output either a true/false based upon that formula. False would prompt me to check that line of data as it would not be matching production vs. project management records. True means the data would be clean throughout all the categories and would be ok moving forward. The formula below is what I tried to do:

=OR(EXACT(D2,Sheet2!\$E\$2:\$E\$10000,MATCH(1,(Sheet2!\$B\$2:\$B\$10000=A2)*(Sheet2!\$C\$2:\$C\$10000=C2)*(Sheet2!\$D\$2:\$D\$10000=B2),0))

Again, I have never done this before and no matter which way I set this up I keep getting #N/As when I CTRL+SHIFT+ENTER

Anyone who would be able to hopefully follow what I'm trying to do and provide to some insight into this problem would be a big help.  Register To Reply

2. ## Re: Index Match formula with Or Exact True/False output

EXACT is to check two text strings, but you have 3 text strings : D2, sheet2 E2:E10000, and MATCH.  Register To Reply

3. ## Re: Index Match formula with Or Exact True/False output

Right, forgot a parenthesis!

=OR(EXACT(D2,Sheet2!\$E\$2:\$E\$10000),MATCH(1,(Sheet2!\$B\$2:\$B\$10000=A2)*(Sheet2!\$C\$2:\$C\$10000=C2)*(Sheet2!\$D\$2:\$D\$10000=B2),0))

Still isn't working though, I feel as if this needs to be setup differently.  Register To Reply

4. ## Re: Index Match formula with Or Exact True/False output Originally Posted by Karroog =OR(EXACT(D2,Sheet2!\$E\$2:\$E\$10000),MATCH(1,(Sheet2!\$B\$2:\$B\$10000=A2)*(Sheet2!\$C\$2:\$C\$10000=C2)*(Sheet2!\$D\$2:\$D\$10000=B2),0))
So now you have:
A = EXACT(...) = TRUE/FALSE
B= MATCH(...) = Order Number
It is not reasonable to compare btw logical and a number, right?  Register To Reply

5. ## Re: Index Match formula with Or Exact True/False output

I couldn't get =OR(EXACT(D2,Sheet2!\$E\$2:\$E\$10000)) to work unless I entered it as an Array. (CNTRL SHFT ENTER)
Since you are entering your formula as an array, you shouldn't need MATCH.

I used this formula
=OR(EXACT(D2,Sheet2!\$E\$2:\$E\$10000),SUM((Sheet2!\$B\$2:\$B\$10000=A2)*(Sheet2!\$C\$2:\$C\$10000=C2)*(Sheet2!\$D\$2:\$D\$10000=B2)))
entered as an array.
You will get TRUE if there is an exact match for D2 in sheet2 E2:E10000 OR if the values in A2, B2 and C2 all appear in the same row somewhere in sheet2 corresponding columns.
Is that what you are looking for?  Register To Reply

6. ## Re: Index Match formula with Or Exact True/False output

This is exactly it! Thank you! That always kills me at times the array vs. non-array formula setup.

Thanks again!  Register To Reply