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

[mctopher]

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:

TestVariable = WorksheetFunction.Match("6950", Sheets("Sheet1").Range("C:C"), 0)

But I cannot get the following to work:

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!

 

[Norie]

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]

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]

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]

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]

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:

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]

Are there multiple instances of Item or Location?

 

[mctopher]

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]

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>

​