test if any value in an array is present in another other array?

[osmdian2]

Hey Guys,

I have a question: Is there a way to test if any value in an array is present in another array?
So array 1 is:
1
3
4
5

and array 2:

9
8
5

then there should be some signal that some part of array because both have a 5.

I use the following array-formula to match values in C4:C8 that are closest in value to B5 and also fulfil the criteria ($D$4:$D$8=1) and ($G$4:$G$8=F5).

In Cell J6:

Please Login or Register  to view this content.

I need now to prevent that the same match is returned for 2 cells. The way I tried to implement it is to have a column H4:H8 that contains the row number and to add the constraint ($J$4:$J5<>$H$4:$H$8) to make sure that the same rows are not matched twice. However, appearantly excel is not capable of this even if entered as an array formula.

[daddylonglegs]

Try this version in J5 copied down

IF(B5="";"N/A";MATCH(MIN(IF($D$4:$D$8=1;IF($G$4:$G$8=F5;IF(COUNTIF($J$4:$J4;$H$4:$H$8)=0;ABS($C$4:$C$8-B5)))));IF($D$4:$D$8=1;IF($G$4:$G$8=F5;IF(COUNTIF($J$4:$J4;$H$4:$H$8)=0;ABS($C$4:$C$8-B5))));0))

confirmed with CTRL+SHIFT+ENTER

I'm asuming that H4:H8 contains the numbers 1 2 3 4 5

If you want you could leave H4:H8 blank and use this in place of $H$4:$H$8 in the formula

ROW($D$4:$D$8)-ROW($D$4)+1

[osmdian2]

thanks, works like a charm!