Match vertical then find match horizontally

[bgrove]

Hi,

Need additional brain power on this one.

I'm trying to use conditional formatting formulas to find the Sales Name AND match to the Sales Acct. IF the name cannot be found the row highlights red. If the Name is found but the account cannot the row highlights red.

I've tried his formula and it does not work
=and(VLOOKUP(f2,'ADM'!$D$2:$D$102,1,0)=f2,VLOOKUP(g2,'ADM'!$E$2:$E$102,1,FALSE)=g2)

I've tried this formula and it does not work:

=and(match(f2,l2:l12,0),match(g2,m2,m12,0))


Here is my data:

Sales Name Sales Acct ____________ Master Sales Name ___ Master Acct
John Doe---------64540 _____________ Cindy Lu----------------------1015
John Doe---------10039818___________ John Doe----------------------1039818
Jane Doe---------10140184___________ John Doe----------------------64540
Jane Doe---------64554 _____________ Jane Doe-----------------------10140184
Cindy Lu----------1015 ______________ Jane Doe-----------------------64554

I would love some suggestions or additional brain power on this one.

Thank you,

Bev

 

[FDibbins]

Maybe try the INDEX/MATCH/MATCH combo?

 

[bgrove]

Maybe try the INDEX/MATCH/MATCH combo?

Would you happen to have an example of the syntax of INDEX/MATCH/MATCH?