# Index / match - 2 match criteria

#### dunmore83

##### Well-known Member
Hi,

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

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

### 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

Try

Array formula
Ctrl+Shift+Enter

M.

#### dunmore83

##### Well-known Member
Excellent - thanks!!!

#### dunmore83

##### Well-known Member
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
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)
Ctrl+Shift+Enter

M.

Last edited:

Replies
6
Views
46
Replies
8
Views
107
Replies
1
Views
74
Replies
7
Views
78
Replies
4
Views
204