Issue with MATCH

[shakeregg]

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

Can provide more info if needed.

Cheers for any help people can provide!

Matt

 

[oldbrewer]

are you certain there are 0 or 1 perfect matches ?

 

[oldbrewer]

								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)

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[DanteAmor]

Use this:

Excel Workbook
	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
sheet1

 

[Aladin Akyurek]

[…]

Can provide more info if needed.

[…]

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