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

blbat

Active Member
Joined
Mar 24, 2010
Messages
292
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!!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,669
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

Active Member
Joined
Mar 24, 2010
Messages
292
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Active Member
Joined
Mar 24, 2010
Messages
292

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Mar 26, 2014
Messages
47

ADVERTISEMENT

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

Active Member
Joined
Mar 24, 2010
Messages
292
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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...


You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top