John,

This is the correct syntax for the AND

=IF(B6="","",IF(AND(B6=Sheet1!$C$4,E6=Sheet1!$E$4) ,ROW(),""))

Mike

"John Moore" wrote:

Hi, not quite what I am looking for ,,,, I need the data to look something
like this ...
formula being in Sheet2

=IF(B6="","",IF(B6=Sheet1!$C$4,AND(E6=Sheet1!$E$4, (ROW(),""))

so that it returns the row number in Sheet 2 based on the criteria in cells
C4 and E4 in Sheet 1 ......

Sheet 1 cell C4 contains name, cell E4 contains type ,,, in Sheet 2 column B
contains numerous entries with various names, column E contains various
types, so the formula would show only the row numbers that meet the two
criteria ,,, e.g. row 6, 10, 15, 25 etc etc etc


"Mike H" wrote:

John,

I'm not sure I understand what you mean but maybe this

=IF(B6="","",IF(B6=Sheet1!$C$4,ROW()&" "&ROW(Sheet1!$C$4),""))

Mike

"John Moore" wrote:

Hi guys, I am trying to establish if I can use the AND function with IF in
the following formula. The formula below retuns a row number based on the
criteria B6 in current sheet ( Sheet 2 ) if it matches the data in cell C4 of
Sheet 1, I would like the formula to return the same data ( row number )
based in C4 of Sheet 1 and E4 of Sheet 1 ,,,, I've tried adding an AND
statment but couldn't get it to work.




=IF(B6="","",IF(B6=Sheet1!$C$4,ROW(),""))