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

#### blbat

##### Active Member
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!!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

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.

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.

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

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

Native row number...

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)?

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.

(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!

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.

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.

Replies
0
Views
431
Replies
1
Views
229
Replies
14
Views
2K
Replies
2
Views
1K
Replies
0
Views
1K

1,220,011
Messages
6,151,445
Members
451,028
Latest member
greekness1

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

### Which adblocker are you using?

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

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