Find row in another sheet with multiple criteria not looping

[impresxy]

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

[kasan]

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:

Please Login or Register  to view this content.

[jindon]

No loop.

Please Login or Register  to view this content.

[impresxy]

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 ?

[impresxy]

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)

[jindon]

Try change to

Please Login or Register  to view this content.

[impresxy]

wow Amazing