Index / match - 2 match criteria

dunmore83

Well-known Member
Joined
Aug 24, 2011
Messages
518
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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

Well-known Member
Joined
Aug 24, 2011
Messages
518
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,721
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top