Creating a Lookup or Match Formula that uses Multiple Criteria

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)),""),""))

View attachment 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.

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) ....................................................................................................................................................................=

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?

Randi said:

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?

Click to expand...

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?

Perhaps something like this? Pls see the file attach....

Rizky said:

Perhaps something like this? Pls see the file attach....

Click to expand...

Or if you simply want to copy across col H irrespective as in your file, perhaps this less complicated approach:

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.