Results 1 to 7 of 7

Thread: Creating a Lookup or Match Formula that uses Multiple Criteria

  1. #1
    Neophyte Randi's Avatar
    Join Date
    Jan 2015
    Location
    Saskatchewan
    Posts
    2
    Articles
    0

    Post Creating a Lookup or Match Formula that uses Multiple Criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am having issues with this formula,

    =IF(INDEX('2006'!$E:$E,MATCH($A2,'2006'!$A:$A,0))="Y",IF(INDEX('2006'!$F:$F,MATCH($A2,'2006'!$A:$A))="",INDEX('2006'!B:B,MATCH($A2,'2006'!$A:$A,0)),""),""))

    Formula help.xls

    I want it to find the Tag number on the previous sheet, and if the two criteria are met, populate the next years sheet with the corresponding data. For the most part it is working, but when I have two Tag numbers (say an old number 16 and a new number 16), it won't return the data on the one that meets the criteria, it leaves the cells blank. I need to have both numbers, because of other data that is added in my bigger spreadsheet. I have also tried putting the valid data first, and last, but it doesn't seem to make a difference...

    The formula is the same in the 'Raised', 'Cow Birth Year' and 'Cow Desc' columns, and in the 'Age at Breeding' column it is the same except I also ask it to add another year rather than copying the previous years data exactly.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hello
    I hope I have understood you, but here goes:
    In your first IF condition a TRUE is only returned if column E of the matching row in !2006 is equal to "Y". If this cell returns anything else (e.g. "" or "N") then the active path in the formula will skip past all the parts that it would process if TRUE was returned.

    =IF(INDEX('2006'!$E:$E,MATCH($A2,'2006'!$A:$A,0))="Y",IF(INDEX('2006'!$F:$F,MATCH($A2,'2006'!$A:$A))="",INDEX('2006'!B:B,MATCH($A2,'2006'!$A:$A,0)),""),""))
    ====================================
    IF FALSE ABOVE, THESE EXPRESSIONS NOT PROCESSED ...==================================================================
    SO WE SKIP HERE (SO NO VALUE is RETURNED) ....................................................................................................................................................................=
    Last edited by Hercules1946; 2015-01-03 at 10:33 PM.

  3. #3
    Neophyte Randi's Avatar
    Join Date
    Jan 2015
    Location
    Saskatchewan
    Posts
    2
    Articles
    0
    Yes, I think you understood what I was saying, and I do understand what you are saying. Now I need to figure out a formula that will look for the duplicate that is valid, if that makes sense? Or is it possible?

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Randi View Post
    Yes, I think you understood what I was saying, and I do understand what you are saying. Now I need to figure out a formula that will look for the duplicate that is valid, if that makes sense? Or is it possible?
    Hello Randi
    Im not sure what you mean by "the duplicate" which is why I didn't try to amend your formula. At present you are looking for a "Y" in Col E (2006!) and your not returning anything if you dont find one.
    This is why your formula cells in 2007! are sometimes blank, i.e. where you have "N" or blank in Col E 2006!. Perhaps you can start with an example of what you actually want to return if these other values are
    encountered?

  5. #5
    Perhaps something like this? Pls see the file attach....
    Attached Files Attached Files

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Rizky View Post
    Perhaps something like this? Pls see the file attach....
    Or if you simply want to copy across col H irrespective as in your file, perhaps this less complicated approach:
    Attached Files Attached Files

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Randi
    although there are no duplicate tags in your 2007! sheet, I see that there are in 2006!, but these can't be included unless you have unique keys listed in both sheets for each one. So for example, if you wanted
    both tag 15 records from 2006! including in !2007 you would need both tags in !2007 as well and then (e.g.) index on the "tag" and "raised" cols concatenated to make a unique key. There are other ways using array formulae,
    but I dont know a way to make that work in a "many to many" matching situation.

    I still feel that I need you to elaborate on what results you want to see before suggesting a solution however, as Im not a great fan of trial and error. Ill leave that with you.

Posting Permissions

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