vlookup with additional criteria needed

[fvisions]

I have a code with several different vlookups, all of them are straight forward except one. the vlookup below is ok accept in the worksheet SAP ECHL...there are sometimes two rows with the same loan number. One is "Active" and the other is "Deactivated". How can I update the code to ignore any "Deactivated" ones? the status is found in column H of the worksheet "SAP ECHL and CT02"

Range("W2").Select
    Application.CutCopyMode = False
    Range("W2:W" & LastRow).FormulaR1C1 = _
        "=VLOOKUP(RC[-21],'SAP ECHL and CT02'!C[-19]:C[-11],9,0)"
    Range("W2:W" & LastRow).Value = Range("W2:W" & LastRow).Value

 

[steve the fish]

You cant use VLOOKUP for that. Do you have XLOOKUP?

 

[fvisions]

no, never heard of Xlookup?

 

[steve the fish]

Ok try something like this:

=INDEX('SAP ECHL and CT02'!L:L,MATCH(1,INDEX(('SAP ECHL and CT02'!D:D=B2)*('SAP ECHL and CT02'!H:H="Activated"),0),0))

 

[fvisions]

Ok try something like this:

=INDEX('SAP ECHL and CT02'!L:L,MATCH(1,INDEX(('SAP ECHL and CT02'!D:D=B2)*('SAP ECHL and CT02'!H:H="Activated"),0),0))

When I copy this code get compile error Expected: end of statement on the word "Activated" tried "Active" and received the same error message

 

[steve the fish]

Its a formula. It needs to be put into the workbook. Check its working. It not code.

 

[fvisions]

Its a formula. It needs to be put into the workbook. Check its working. It not code.

ok sorry I mis-understood. I need vba code to update the process as the form button does multiple steps including the one mentioned in the post.

 

[fvisions]

I figured out a workaround. I am going to update the VBA code to sort the worksheet by status then the vlookup will pickup the Active data first. Thank you for your help.

 

[steve the fish]

You put the formula in worksheet. Check it works. Once happy you then record the formula going into the cell. This will give you the necessary code. You have use the macro recorder? You will need to use it sometimes so its good practice.

 

[fvisions]

You put the formula in worksheet. Check it works. Once happy you then record the formula going into the cell. This will give you the necessary code. You have use the macro recorder? You will need to use it sometimes so its good practice.

yes I record marcos to start with and move forward from there. When I get stuck, this forum is my go-to website.