Lookup / Match function

[Peter]

Hi,

I am looking for any kind of function that will enable the following:-

Sheet 1
Col A Col B Col C Col D Col E Col F
Name Inv No Date Value Job No Payment due Date
XYZ 1234 1/2/08 £1500 1000 4/7/08


Sheet 2
Col A Col B Col C Col D Col E Col F
04/07/2008 Name Invoice No Date Value Job No
XYZ 1234 1/2/08 £1500 1000

Formula to show - if date on sheet 1 is less than or equal to date on sheet
2 then show data in respective fields on sheet 2.

I have tried =if(sheet 1!F2<=sheet 2!a1,sheet 1!A2:a50,"") but returns #Value.

Any suggestions

 

[edvwvw via OfficeKB.com]

In B2 Sheet 2 try the following

=IF(AND(Sheet2!$F$2<A2),Sheet1!A2," ")

drag to the right for Inv No , Date, Value, Job No.

edvwvw

 

[edvwvw via OfficeKB.com]

I must have been asleep when I looked at this - the correct formula is:

'=IF(AND(Sheet2!$A$2>Sheet1!$H$2),Sheet1!A2," ")

edvwvw

In B2 Sheet 2 try the following

=IF(AND(Sheet2!$F$2<A2),Sheet1!A2," ")

drag to the right for Inv No , Date, Value, Job No.

edvwvw

Hi,

[quoted text clipped - 16 lines]

Any suggestions