Index / match - 2 match criteria

[dunmore83]

Hi,

I have the following formula which is returning a #REF! error;

=INDEX('Paddock-Plan-Crops'!$V$17:$V$67,MATCH(S$7,'Paddock-Plan-Crops'!$G$17:$G$67,0),MATCH(S$4,'Paddock-Plan-Crops'!$Y$17:$Y$67,0))

Basically I need the formula to return the value from column V only when both of the Match criteria's are found.

Any helpers on how to fix this one ??

Thanks

 

[Marcelo Branco]

Try

Array formula
=INDEX('Paddock-Plan-Crops'!$V$17:$V$67,MATCH(1, IF('Paddock-Plan-Crops'!$G$17:$G$67=S$7,IF('Paddock-Plan-Crops'!$Y$17:$Y$67=S$4,1)),0))
Ctrl+Shift+Enter

M.

 

[dunmore83]

Excellent - thanks!!!

 

[dunmore83]

Actually I need another modification to that formula!!

Can you help ??

The Match searches can have duplicate results i.e. S7 = "Pivot 2" S4 = "Silage"

"Pivot 2" can occur twice in G17:G67 & "Silage" can occur twice in Y17:Y67 - when it does I need the formula to return the Sum of both numerical values found in Column V17:V67

Currently it is only returning the sum of the 1st match.

Any ideas ??

 

[Marcelo Branco]

I have to leave now, so i'll say just the idea (UNTESTED)
Try INDEX(V:V, SMALL(If(condition1,IF(conditon2,ROW(V$17:V$67))),ROWS(A$2:A2)))

Something like (not sure about the parentheses)

Array formula in A2 copied dow (assuming the first cell you put the formula is A2 - adjust)
=IFERROR(INDEX('Paddock-Plan-Crops'!$V:$V,SMALL(IF('Paddock-Plan-Crops'!$G$17:$G$67=S$7,IF('Paddock-Plan-Crops'!$Y$17:$Y$67=S$4,ROW('Paddock-Plan-Crops'!$V$17:$V$67))),ROWS(A$2:A2))),"")
Ctrl+Shift+Enter

M.