+ Reply to Thread
Results 1 to 6 of 6

Index Match formula with Or Exact True/False output

  1. #1
    Registered User
    Join Date
    08-08-2007
    Posts
    80

    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.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,477

    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.

  3. #3
    Registered User
    Join Date
    08-08-2007
    Posts
    80

    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.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,477

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

    Quote Originally Posted by Karroog View Post
    =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?

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,988

    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?
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    08-08-2007
    Posts
    80

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. lookup exact match returns TRUE or FALSE
    By quade_1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2016, 02:21 AM
  2. [SOLVED] Index Match Formula doesn't recognize output from Right Formula as lookup value.
    By GoGators in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-10-2015, 11:33 AM
  3. [SOLVED] Changing EXACT funtion value from TRUE/FALSE to OK/NG
    By namialus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-05-2013, 02:01 AM
  4. Change default checkbox output true false into text
    By safonso in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-02-2013, 03:31 PM
  5. [SOLVED] Match text in 2 columns, if true output adjacent cell to the match.
    By kenoboy in forum Excel General
    Replies: 3
    Last Post: 07-03-2012, 09:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts