Formula for =IF(AND(MATCH( criteria )) ?

[blbat]

Excel 2010
Trying to match two values, one in each column, so if the combination of Cell A2 and Cell B2 MATCH anywhere in Column D and E.

I have two MATCH formulas that function perfectly...but I want to combine them using an "AND".... if possible.

=MATCH($A2, $D$1:$D$702, 0)

=MATCH($B2, $E$1:$E$702, 0)

Column "A" is Names
Column "B" is Numbers

Column "D" is a larger set of those same names, with duplicates
Column "E" is a larger set of those same numbers, with duplicates


Sorry cant post clean data via html genie, but here is a rough idea of what it looks like:

you will see "SMITH" and "14003" is the first match across the four columns


col A col B col D col E
names numbers names2 numbers2
SMITH 14003 SMITH 14026
SMITH 14002 SMITH 14001
SMITH 14001 SMITH 14027
SMITH 14004 Rogers 14029
JONES 14005 Rogers 14030
JONES 14006 Rogers 14018
JONES 14007 Rogers 14032
JONES 14008 JONES 14033
JONES 13009 JONES 14034
JONES 14010 JONES 14035
Rogers 14018 JONES 14036
Rogers 14012 JONES 14037
Rogers 14013 JONES 14038
Rogers 14014 SMITH 14039
Rogers 12015 SMITH 14003


I know this does not work, but it gives an idea of what I'm trying to accomplish:

=IF(AND(MATCH(($A2, $D$1:$D$702, 0)),MATCH($B2, $E$1:$E$702, 0)))

any help would be greatly appreciated!!

 

[Misca]

If I've understood it correctly you're trying to figure out if values in A & B columns can be found from columns C & D.

If that is the case try something simple as =MATCH($A1,$C$1:$C$15,0)*MATCH($B1,$D$1:$D$15,0)>0

 

[blbat]

Thanks Misca...That does give me a TRUE for any Matches it finds, but not the relative location of the found Matches.

I need to figure out how to get INDEX to work with the formula you used.

 

[Aladin Akyurek]

What you are after is not very clear. If not the following, please elaborate:

E2, control+shift+enter, not just enter, and copy down:

=ISNUMBER(MATCH($A2&"|"&$B2,$C$2:$C$16&"|"&$D$2:$D$16,0))

which indicates whether a pair like A2 and B2 occurs together in C:D.

 

[blbat]

thanks for taking the time to drop in on this thread Aladin!

your solution also works, but was looking to return the Row Number instead of just "TRUE".

is that possible?

I apologize for not being clear, I can't use Excel Genie on this computer, so it's difficult.

so, trying to be clear with just words, I get a "TRUE" returned for cells "A2 & B2", because "C16 & D16" provide the Match.

Now, my quest is to get the Row number returned to me instead of just TRUE. (it would be "16" in this example)

regards,

blbat8541

 

[Aladin Akyurek]

thanks for taking the time to drop in on this thread Aladin!

your solution also works, but was looking to return the Row Number instead of just "TRUE".

is that possible?

I apologize for not being clear, I can't use Excel Genie on this computer, so it's difficult.

so, trying to be clear with just words, I get a "TRUE" returned for cells "A2 & B2", because "C16 & D16" provide the Match.

Now, my quest is to get the Row number returned to me instead of just TRUE. (it would be "16" in this example)

regards,

blbat8541

The solutions which are offered do differ!...

Relative row number...

=IFERROR(MATCH($A2&"|"&$B2,$C$2:$C$16&"|"&$D$2:$D$16,0)),"Not Found")

Native row number...

=IFERROR(MATCH($A2&"|"&$B2,$C$2:$C$16&"|"&$D$2:$D$16,0))+ROW($C$2)-1,"Not Found")

 

[TimFoley]

If I understand correctly you want to find where the values in D & E are the same as those in A and B. I am not sure the MATCH function will work correctly, given that the data in D & E can contain duplicates.

Could you add a column F that concatenates D & E? Then you could use IF(Match($A2&$B2, $F$1:$F$702)). Otherwise, could you use If(countifs($A2,$D$1:$D$702,$B2,$E$1:$E$702)>0)?

 

[blbat]

Aladin-

Native row number is what I was after! your solution worked fine- at first I was getting the "TOO FEW ARGUMENTS" complaint from Excel...then I removed the second Parenthesis before the ROW argument and it functioned perfectly.

=IFERROR(MATCH($A2&"|"&$B2,$C$2:$C$16&"|"&$D$2:$D$16,0)+ROW($C$2)-1,"Not Found") C+S+E

(I figure you were typing so fast that an extra got in there!)

SOLVED.

p.s., timfoley...thanks, I was trying hard not to use any helper columns!

 

[Aladin Akyurek]

Aladin-

Native row number is what I was after! your solution worked fine- at first I was getting the "TOO FEW ARGUMENTS" complaint from Excel...then I removed the second Parenthesis before the ROW argument and it functioned perfectly.

=IFERROR(MATCH($A2&"|"&$B2,$C$2:$C$16&"|"&$D$2:$D$16,0)+ROW($C$2)-1,"Not Found") C+S+E

Right. That's the idea.

(I figure you were typing so fast that an extra got in there!)

Yes, something like that...

SOLVED.

You are welcome.