IF ARRAY with multiple conditions.

[Jay07]

Need help with this formula... An example of data split between tw
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 i
column A of 'Sheet 1'

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

This would make cells C3 & C6 say 'Possible Match' as both of the date
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 "Possibl
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 dat
in A6 can be found in the range of dates in 'Sheet 1' TWICE but only B
the same value of the matching date.



Hope that makes sense.

Thanks in advance.

Ja

 

[Stunn]

Try this:

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