Find Match Based on Values on Multiple Columns

Hi everyone,

I had a trouble to get the row number of row that has match based on values on multiple columns. The formula Application.Match(strSearch, searchRange, 0) works for a single value match. I am looking to do something like this Application.Match(strSearch1 & strSearch2 , searchRange1 & searchRange2, 0) but it does not work.

In the Excel itself, we can use MATCH(A1&A2,A1:A10&B1:B10,0), so I can write Evaluate("MATCH(A1&A2,A1:A10&B1:B10,0)"). But, I do not have a fixed search range, I do not want it to always be A1:A10 and B1:B10, I need a dynamic range, such as from the first row to the last non-blank row, and it will change according to different data. So I need to find the last non-blank row using a VBA formula, and then include it in the range. But I could not find a way to write the formula, it always gives me a Syntax error.

Thank you for your help!!

Sounds strange though...MATCH(A1&A2
Something like

Please Login or Register  to view this content.

Where LastR is the last row of Col.A or Col.B

Thank you jindon!!

Your code works very well! However, I actually wants to match for a string that is defined inside VBA itself, not referencing to cell A1 and A2 in the Excel Workbook.

[IN VBA]
a="FIRST"
b="SECOND"

I tried something like
Evaluate("MATCH(" & """ & a & """" & "&" & """ & d & """ & ",A1:A" & LastR & "&B1:B" & LastR & ",0)")

But it does not work..

Do you know why? Thank you very much!!

Try

Please Login or Register  to view this content.

Hi Jindon,

Yes your code works thankyouu! I also just found out that I can use this formula

Evaluate("MATCH(" & """ & a & """" & "&" & """ & d & """ & ",A1:A" & LastR & "&B1:B" & LastR & ",0)"), but changing the """ into chr(34) instead.

Is this because VBA reads the double quote in a certain way that it interprets my previous code wrongly?

Inside Brackets of Evaluate method should be String that you enter the formula in a cell.

You should have opening " and ending ", so any " used as A string must be escaped by another set of ".

Match("blahblah",A1:B10&B1:B10,0)
Becomes
"Match(""blahblah"",A1:A10&B1:B10)"