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]

[TABLE="width: 704"]
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bob smith[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]bob jones[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]row 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tom jones[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b jones[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]rob jones[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bob jones[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]14 obtained by[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bill green[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=OFFSET($A$4,MATCH(E5,$A$5:$A$100,0),1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.