Excel vba - find row number where colum data (multiple clauses) -

i need function in vba finds row number based on 2 clauses.

here excel sample:

**          b      c      d**   1    id1    day1   val1   xxx   2    id2    day1   val2   xxx   3    id3    day1   val3   xxx   4    id1    day2   val1   xxx   5    id2    day2   val2   xxx   6    id3    day2   val3   xxx 

i need find row number (in case row number 2) b = "day1" , = "id2".

based on row number, need further values of other columns, i.e. c2, d2

hope question clear.

thank you!

with data setup that, can use match function row number:

=match(1,index(($a$1:$a$6="id2")*($b$1:$b$6="day1"),),0) 

if there no matches criteria, formula return #n/a error. can change criteria cell references, example:

=match(1,index(($a$1:$a$6=f1)*($b$1:$b$6=g1),),0) 

for second part of question, returning values found row number, can use index function return value column. pretending match formula in cell h1, these 2 formulas return value column c , d respectively:

=index($c$1:$c$6,h1) =index($d$1:$d$6,h1) 

alternately, put single formula:

=index($c$1:$c$6,match(1,index(($a$1:$a$6=f1)*($b$1:$b$6=g1),),0)) 

and if don't want looking @ errors, can use iferror on excel 2007+

=iferror(index($c$1:$c$6,match(1,index(($a$1:$a$6=f1)*($b$1:$b$6=g1),),0)),"no matches") 

error checking excel 2003 , below:

=if(isna(match(1,index(($a$1:$a$6=f1)*($b$1:$b$6=g1),),0)),"no matches",index($c$1:$c$6,match(1,index(($a$1:$a$6=f1)*($b$1:$b$6=g1),),0))) 

[edit]: including vba solution per user request. uses find loop, efficient , flexible, , shows how extract values other columns once match has been found:

sub tgr()      dim rngfound range     dim strfirst string     dim strid string     dim strday string      strid = "id2"     strday = "day1"      set rngfound = columns("a").find(strid, cells(rows.count, "a"), xlvalues, xlwhole)     if not rngfound nothing         strfirst = rngfound.address                     if lcase(cells(rngfound.row, "b").text) = lcase(strday)                 'found match                 msgbox "found match at: " & rngfound.row & chr(10) & _                        "value in column c: " & cells(rngfound.row, "c").text & chr(10) & _                        "value in column d: " & cells(rngfound.row, "d").text             end if             set rngfound = columns("a").find(strid, rngfound, xlvalues, xlwhole)         loop while rngfound.address <> strfirst     end if      set rngfound = nothing  end sub