VBA code for checking multiple criteria

Hi,

Am trying to find row number by matching multiple criteria without the use of array function and using the below code.

This works perfectly in excel (without the worksheetfunction command) but does not work in VBA. Can someone please help with the code

Range("L5").Value = WorksheetFunction.IfError(WorksheetFunction.Match(1, Application.WorksheetFunction.Index((Range("A1:A10") = "c") * (Range("B1:B10") = "a"), 0, 1), 0), "")

Thanks Carim.

Attached is the file.

Am trying to get the row number if value in column 'A' = "c" and value in column 'B' = "a" .

And want to do this with the array formula (ie without the cntrl + shift + enter)

Hi Carim,

Expected result is that Cell 'F6' will have value 7.

This is the row that has value "c" in column 'A' and value "a" in column 'B".

That is the forumla - Range("F6").Value = ....

Have highlighted the cells.

Sorry you are correct. updated it

Awesome! This works perfectly Carim.

I did not find this way to use Match in any of the online docs.

Any website that you suggest i can refer to.

Two more questions -

1) If there are multiple rows that have value "c" and "a" (like in attached sheet), then how do i get all row numbers. Like in Cell F3 get value 7 and in cell F3 get value

2) If I want to get rows which have either 'c' or 'b' in column 'A' and value 'a' in column 'B' then how do i get those

I tried using 'OR' instead of '&' in the match formula but that gives and error.

Thanks Again!

This is fantastic.

Never thought i would be able to get help so quickly.

Thanks a lot Carim!