Domenic,

This one is great! Both work without array-entering (I don't need to see the
values to manipulate them). Apart from that the second formula is relatively
short it also allows to use the cell references dynamically.

Cheers,
KL

"Domenic" wrote in message
...
Twice I've tried to post my reply and both times it seems I was
unsuccessful. Here goes a third time, hopefully without any problems...
:)

Try the following array formula that needs to be block-entered:

=VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0)=CHOOS
E({1;2;3;4;5},B1,F1,G1,H1,J1)

Or to have the formula return TRUE or FALSE, depeding on whether each
value in the first array equals the second array...

=SUMPRODUCT(--(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0)=CHOOS
E({1;2;3;4;5},B1,F1,G1,H1,J1)))=5

Hope this helps!

In article ,
"KL" <lapink2000(at)hotmail.com (former ) wrote:

Hi there,

I am trying to compare two fixed arrays and each of the two parts of the
formula seem to return the correct values, but the equation itself
doesn't
seem to work as desired:

=VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1","F1","G1","H1"
,"J1"})

when pressing F9 while in formula bar it looks like this:

={"d";"d";"d";"d";"d"}={"d","e","d","d","d"}

but the final result is:

={#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,
#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,
#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,
#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!,#V ALUE!}
(looks like each element in one array is compared to each element in the
other)

which yields =TRUE (???)

If I use SUMPRODUCT:

=SUMPRODUCT(--(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1",
"F1","G1","H1","J1"})))

the interim results are the same, only the final result is =#VALUE!

Both formulas work identically whether array-entered or not.

What I am trying to do is to compare the two arrays in this way:

d=d=TRUE
d=e=FALSE
d=d=TRUE
d=d=TRUE
d=d=TRUE
=FALSE

Any ideas please?

Thanks,
KL