Need help with this formula... An example of data split between two sheets...

'Sheet 1'

|----A----|----B----
1 18/01/12-----1
2 14/05/10-----2
3 12/10/11-----5
4 26/01/08-----5
5 10/11/12-----4
6 04/02/09-----7
7 10/11/12-----9

'Sheet 2'

|----A----|----B----
1 16/11/12-----2
2 07/12/10-----2
3 26/01/08-----3
4 09/08/12-----1
5 30/06/08-----6
6 10/11/12-----4
7 03/05/11-----1

In 'Sheet 2' I want to look up the date in A1 against all the dates in column A of 'Sheet 1'

I know to do this is to put in C1 =IF(A6=(Sheet1!$A$1:$A$6),"Possible Match","No Match")

This would make cells C3 & C6 say 'Possible Match' as both of the dates in A3 & A6 can be found in the range of dates in 'Sheet 1'

Following on from that, all cells in column C which then read "Possible Match", I need to look up the value in colum B.

If that's do-able then only C6 should read "Possible Match" as the date in A6 can be found in the range of dates in 'Sheet 1' TWICE but only B6 the same value of the matching date.



Hope that makes sense.

Thanks in advance.

Jay

Any chance of a sample workbook, please?
Makes it far easier to provide a useful and prompt reply...

Try this:

=IF((Sheet1!$A$1:$A$5=$A3)*(Sheet1!$B$1:$B$5=Sheet 2!$B3),"Possible Match","No Match")