VBA Match Statement With Two Conditions?

[Ark68]

I am looking for a VBA solution to find the row number in which two conditions exist. (Match function perhaps ... I've only ever used it with one condition)

Condition one , column H must equal variable pnum
AND
Condition two, column B must equal variable facname

 

[JoeMo]

Here's an example. Change addresses and lookup values to suit.

Book1.xlsm
	A	B	C	D
1	23	19
2	13	18		6
3	1	1
4	15	16
5	2	2
6	9	12
7	17	9
8	23	15
9	19	3
10	1	24
Sheet30

Cell Formulas
Range	Formula
D2	D2	=MATCH(9&"|"&12,$A$1:$A$10&"|"&$B$1:$B$10,0)

 

[Ark68]

Thank you JoeMo for providing me some assistance. As I was looking for a VBA solution, I tried to adapt your formula to VBA without any luck. I'm getting a "Type mismatch." error with the line in red.

Sub signatures(srow As Integer, pnum As String, fac2 As String)
    Dim cd_srow As Integer
    Stop
    cd_srow = Application.WorksheetFunction.Match(pnum & "|" & fac2, ws_cd.Columns(17) & "|" & ws_cd.Columns(6), 0)
    MsgBox sd_row
End Sub

I am trying to get the row number (cd_srow) of the row in which their is a match to 'pnum' in column Q (17) AND 'fac2' in column F (6) of workshhed ws_cd.

Thoughts?

Also, how many conditions can I build into this statement?

 

[JoeMo]

Thank you JoeMo for providing me some assistance. As I was looking for a VBA solution, I tried to adapt your formula to VBA without any luck. I'm getting a "Type mismatch." error with the line in red.

I am trying to get the row number (cd_srow) of the row in which their is a match to 'pnum' in column Q (17) AND 'fac2' in column F (6) of workshhed ws_cd.

Thoughts?

Also, how many conditions can I build into this statement?

For a VBA solution try:

If Application.Match(pnum, ws_cd.Columns(17), 0) = Application.Match(fac2, ws_cd.Columns(6), 0) Then
    cd_srow = Application.Match(pnum, ws_cd.Columns(17), 0)
Else
    Msgbox "No match"
End If

 

[Ark68]

Hi Joe, just acknowledging your help. I'm not getting any errors, but until I get another prominent issue resolved, I'm unsure how accurate the results are with my testing. I'll report back!

 

[Ark68]

Hi all ...
I've deviated a bit of my OP. I am looking for the one row (in worksheet ws_cd) that matches 3 criteria. On an assumption that I can expand on JoeMo's suggestion of two criteria to three, I have this code:

If Application.Match(pnum, ws_cd.Columns(17), 0) = Application.Match(fac2, ws_cd.Columns(6), 0) = Application.Match(st, ws_cd.Columns(2), 0) Then
        cd_rrow = Application.Match(pnum, ws_cd.Columns(17), 0)
    Else
        MsgBox "There is no match to the booking in CORE_DATA", , "DATA ERROR: No match"
        Stop
    End If

So in this case I am looking for a unique match in which the row numbers for a match in columns 17, 6 and 2. (Maybe bad explanation there, but hopefully you get my drift).

This is not working. It is either because of my bad assumption of grouping three match criteria into one, OR, my last criteria is failing to produce accurate results. 'st' is a decimal representation of time. ws_cd column 2 holds time values formatted as h:mmA/P. So, I'm not sure if .375 matches 9:00A.

 

[JoeMo]

Hi all ...
I've deviated a bit of my OP. I am looking for the one row (in worksheet ws_cd) that matches 3 criteria. On an assumption that I can expand on JoeMo's suggestion of two criteria to three, I have this code:

If Application.Match(pnum, ws_cd.Columns(17), 0) = Application.Match(fac2, ws_cd.Columns(6), 0) = Application.Match(st, ws_cd.Columns(2), 0) Then
        cd_rrow = Application.Match(pnum, ws_cd.Columns(17), 0)
    Else
        MsgBox "There is no match to the booking in CORE_DATA", , "DATA ERROR: No match"
        Stop
    End If

So in this case I am looking for a unique match in which the row numbers for a match in columns 17, 6 and 2. (Maybe bad explanation there, but hopefully you get my drift).

This is not working. It is either because of my bad assumption of grouping three match criteria into one, OR, my last criteria is failing to produce accurate results. 'st' is a decimal representation of time. ws_cd column 2 holds time values formatted as h:mmA/P. So, I'm not sure if .375 matches 9:00A.

You are looking for exact matches between two numbers, one a decimal, the other time. You should use a rounded version of both with rounding to the same precision. To test, compare the decimal number you think should match a specific time in col 17, say Q10 for example, by comparing the decimal number to =N(Q10). For example, if your decimal number is 0.3870 and your time in Q10 is 9:17:21 AM, then N(Q10) = 0.387048611111111 which is NOT exactly a match to your decimal number, whereas ROUND(N(Q10),3) is an exact match (=0.387).

 

[Ark68]

Thank you JoeMo for that explanation. But in my testing (which I didn't really elaborate on) there should be a match with or without your logical suggestion.
'st' represents 9:00A ... it has a decimal value of 0.3750. In cell C2 of worksheet ws_cd there exists a time value of 9:00A. It's decimal equivalent is 0.3750. Should this not be a match? There is also a match to 'pnum' in cell C17, and a match to 'fac2' in C6, so shouldn't I be getting a match (cd_rrow) = 2?. Perhaps the problem is 'st' has been declared as a variant?

Without your suggestion in this and a few other specific values it should (I want to believe) work, but yes, it will become problematic further on outside of those values, so your suggestion provides a failsafe.

I am stuck though on how to apply the ROUND function to the values in column(2) of the MATCH function.

If Application.Match(pnum, ws_cd.Columns(17), 0) = Application.Match(fac2, ws_cd.Columns(6), 0) = Application.Match(Round(st,3), Round(ws_cd.Columns(2),3), 0) Then

?

 

[JoeMo]

Thank you JoeMo for that explanation. But in my testing (which I didn't really elaborate on) there should be a match with or without your logical suggestion.
'st' represents 9:00A ... it has a decimal value of 0.3750. In cell C2 of worksheet ws_cd there exists a time value of 9:00A. It's decimal equivalent is 0.3750. Should this not be a match? There is also a match to 'pnum' in cell C17, and a match to 'fac2' in C6, so shouldn't I be getting a match (cd_rrow) = 2?. Perhaps the problem is 'st' has been declared as a variant?

Without your suggestion in this and a few other specific values it should (I want to believe) work, but yes, it will become problematic further on outside of those values, so your suggestion provides a failsafe.

I am stuck though on how to apply the ROUND function to the values in column(2) of the MATCH function.

If Application.Match(pnum, ws_cd.Columns(17), 0) = Application.Match(fac2, ws_cd.Columns(6), 0) = Application.Match(Round(st,3), Round(ws_cd.Columns(2),3), 0) Then

?

Each of the 3 match expressions returns the row number in the array that matches the respective lookup value in the respective match expression. So, pnum in col 17 must be in the same row as fac2 in col 6 and so on. Your match expressions are looking for specific values in columns 2, 6 and 17 of the same worksheet. Those match expressions must all return the same row numbers. That's not the case if one match is in C2 and the other in C17.

 

[Ark68]

Hey JoeMo, thanks for your support!

Those match expressions must all return the same row numbers.

They will! That's what the if statement is trying to determine. So in my testing, criteria 1 (pnum) will return row 2, criteria 2 (fac2) will return row 2, and cireteria 3 (st) will return row 2 (if I have the match of time set up right). With all 3 values matching (row 2) the resulting value cd_rrow should equal 2?

This is my interpretation, but perhaps you're trying to explain sometime I'm not quite picking up on, and for that I apologize. I interpret this single match statement as basically ...

v1 = application.worksheetfunction.match(pnum, ws_cd.Columns(17), 0) 'returns row number of first match of pnum in column 17 eg 2
v2 = application.worksheetfunction.match(fac2, ws_cd.Columns(6), 0)    'returns row number of first match of pnum in column 6 eg 2
v3 = application.worksheetfunction.match(st, ws_cd.Columns(2), 0)        'returns row number of first match of st (variant) in column 2 (h:mmA/P)eg 2
if v1 = v2 = v3 then
     cd_drow = v1
     msgbox "The FIRST row in which all three criteria are satisfied is row : " & cd_drow
Else
     msgbox "There is no row that match all three criteria."
Endif

Thank you for your patience.