# Thread: Issue with MATCH Thanks: 0 Likes: 0

1. ## Issue with MATCH

Hey all,

FormR kindly helped with combining two formulas together:

=IF(AND(INDEX(T:T,MATCH(E5,AF:\$AF,0))=G5,INDEX(BJ:BJ,MATCH(E5,AF:\$AF,0))=H5),"Yes","No")

This formula is replicated in Rows until 1500 i.e.

=IF(AND(INDEX(T:T,MATCH(E6,AF:\$AF,0))=G6,INDEX(BJ:BJ,MATCH(E6,AF:\$AF,0))=H6),"Yes","No")

etc etc

The issue that I'm now facing is that on occasions the data inputted in Column E and AF may be the same in multiple cells i.e. Smith, John. As a result it's often incorrectly returning a 'No' as it's picking up the first Smith, John in AF. What I want it do if possible is to continue searching for the exact match (T:T & G5, BJ:BJ & H5 etc) and return a 'No' then if it's not found.

Hopefully that makes sense

Cheers for any help people can provide!

Matt

2. ## Re: Issue with MATCH

are you certain there are 0 or 1 perfect matches ?

3. ## Re: Issue with MATCH

 row 4 bob smith 9 bob jones 14 row 5 tom jones 7 b jones 5 rob jones 12 bob jones 14 14 obtained by bill green 16 =OFFSET(\$A\$4,MATCH(E5,\$A\$5:\$A\$100,0),1)

4. ## Re: Issue with MATCH

Use this:

sheet1

 A E F G H T AF BJ 1 4 Name Formula Data1 Data2 First search Names Second Search 5 John Smith Yes new old without John Smith new 6 Carol No Switch white without John Smith new 7 Sue Yes isla bonita new John Smith old 8 Switch Carol Yellow 9 Switch Carol Pink 10 Switch Carol blue 11 isla Sue can 12 isla Sue bonita 13 isla Sue dessert

Formula
 Cell Formula F5 =IF(SUMPRODUCT((\$AF\$5:AF100=E5)*(\$T\$5:T100=G5)*(\$BJ\$5:BJ100=H5))>0, "Yes","No") F6 =IF(SUMPRODUCT((\$AF\$5:AF101=E6)*(\$T\$5:T101=G6)*(\$BJ\$5:BJ101=H6))>0, "Yes","No") F7 =IF(SUMPRODUCT((\$AF\$5:AF102=E7)*(\$T\$5:T102=G7)*(\$BJ\$5:BJ102=H7))>0, "Yes","No")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

5. ## Re: Issue with MATCH

Originally Posted by shakeregg

[…]

[…]
Try to provide a small sample along with the desired output.