Find row in another sheet with multiple criteria not looping

Hi,

I have been looking for a code that can search a row in sheet2 with 2 critiria specified in sheet1 and return row number. I need also to put "if clause" as there might be possibility that such a search is not existed and that i can assign another code for not found record. I cannot use looping method because my data can be up to 10000 records.

Sheet1
A1: Name
B1: Surname

Sheet2
ColumnA: Name
ColumnB: Surname
ColumnC: Address
ColumnD: Salary

Thanks for help

Hi,
Looping throw the 10k cells isn't good idea, but if you put data from Sheet2 into array - it will decrease time of searching significantly.
For search by two criteria you could use something like:

Array(i) = Criteria1 AND Array(i+1) = Criteria2

Hi Kasan,

Thank you for your reply.
I'm not an expert in VBA. Would you please explain more in detail? How can i set array for sheet 2? and how to get row number for the code you gave ?

No loop.

Sub test()
    Dim x
    With Sheets("sheet1")
        x = Filter(.[transpose(if((sheet2!a1:a20000=a1)*(sheet2!b1:b20000=b1),row(1:20000)))], False, 0)
    End With
    MsgBox IIf(UBound(x) > -1, "Found in" & vbLf & Join(x, vbLf), "No match")
End Sub

Hi Jindon,

Thank you for your reply. As my sheet has been named, i got debug message in this line.
Would you please help ?
x = Filter(.[transpose(if((Sheets("Requirement Status Results")!a1:a20000=D6)*(Sheets("Requirement Status Results")!g1:g20000="VT"),row(1:20000)))], False, 0)

Try change to

x = Filter(.[transpose(if(('Requirement Status Results'!a1:a20000=D6)*('Requirement Status Results'!g1:g20000="VT"),row(1:20000)))], False, 0)

wow Amazing