Using VBA to use "And" with two "Match" formulas

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
I need help getting two "Match" functions (joined with an "And") to work in Excel 2010.

I need to find the row number where two values match. I can get a singular match function to work, but I need both.

The following will work for me correctly:
HTML:
TestVariable = WorksheetFunction.Match("6950", Sheets("Sheet1").Range("C:C"), 0)

But I cannot get the following to work:

HTML:
Test = WorksheetFunction.Match("6950", Sheets("Sheet1").Range("C:C"), 0) and WorksheetFunction.Match("114", Sheets("Sheet1").Range("B:B"), 0)

The last line returns the error:
"Unable to get the Match property of the WorksheetFunction class".

I'm guessing it's a simple syntax issue, could someone help me out with this?

Thanks!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
That error is because one(or both) of the Match functions has failed, it's nothing to do with VBA syntax.

Are you definitely looking for numbers that are stored as text with the Match formulas?
 

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
I see what you're saying, I tested the second Match function and that indeed failed. The values in that column are all numeric, but yes, I believe they're stored as text. Is there something special that has to be done to the formula in this case?

Here is the failing formula:

Test = WorksheetFunction.Match("114", Sheets("Sheet1").Range("B:B"), 0)
 

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
Added info:
The data was stored as numeric, so removing the quotes worked.

Test = WorksheetFunction.Match(114, Sheets("Sheet1").Range("B:B"), 0)

That said, when the two formulas are combined instead of a failure notice it returns "0", which is incorrect because there is a valid match on row 60556.

Is there something I have to do to the formula to make sure it looks for all matches? It seems like it looks for a place where the first criteria is met, then tests the second criteria, and if there is no match then it stops. Is there a trick to force it to review all data until a match is found? My only other thought was an "If" statement that goes over every line until the end. Suggestions?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm a bit confused, Match will return the first match it finds in the range you tell it to look in and there is no 2nd criteria.

What is it you are trying to do?
 

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
I'm a bit confused, Match will return the first match it finds in the range you tell it to look in and there is no 2nd criteria.

What is it you are trying to do?

I need to look at my first workbook to determine Item and Location. Then I look at my second workbook and find the row that has both the Item and Location, then when I find that row return one of the values on that row into my variable. I thought I could do that with the following:

HTML:
Test = WorksheetFunction.Match("6950", Sheets("Sheet1").Range("C:C"), 0) and WorksheetFunction.Match(114, Sheets("Sheet1").Range("B:B"), 0)

Is there a better way to get what I'm looking for?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are there multiple instances of Item or Location?
 

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
Yes, there are multiple instances of the item number and multiple instances of the part number, but only one instance where both match. There are about 70,000 parts that could be in 14 different locations, so you have about 980,000 rows of data in which you're trying to find the one where both item and location match up. I can do this with an IF / AND statement that evaluates each row, but processing line by line is so very slow I was hoping MATCH or something else may be more efficient.
 

bk1702

New Member
Joined
Jul 18, 2012
Messages
38
Try using an array formula:
http://www.mrexcel.com/articles/CSE-array-formulas-excel.php


Example Formula in C5:

=INDEX(Sheet1!E3:E5,MATCH(Sheet1!C2&Sheet1!C3,Sheet1!F3:F5&Sheet1!G3:G5,0)) hit control, shift enter to enter formula in cell to get a result.
A</SPAN>
B</SPAN>
C</SPAN>
D</SPAN>
E</SPAN>
F</SPAN>
G</SPAN>
2</SPAN>
Item 1</SPAN>
Two</SPAN>
Column A</SPAN>
Item 1</SPAN>
Item 2</SPAN>
3</SPAN>
Item 2</SPAN>
One</SPAN>
abc</SPAN>
One</SPAN>
Two</SPAN>
4</SPAN>
def</SPAN>
Two</SPAN>
One</SPAN>
5</SPAN>
Result from Column A</SPAN>
def</SPAN>
ghi</SPAN>
One</SPAN>
Three</SPAN>

<TBODY>
</TBODY>


 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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