KL,

Well, one problem is that you are comparing a vertical array with a
horizontal array.

Try using commas instead of semicolons in your first array. That takes care
of the large resultant array, and when you F( your way through, it works
better.

But I don't have any luck array entering the INDIRECT part into multiple
cells, so there may be some problem using that in an array formula. But
Harlan Grove will weigh in soon, to correct any mistakes I make here <vbg

Other than that, I can't help you.

Bernie
MS Excel MVP


"KL" <lapink2000(at)hotmail.com (former ) wrote in
message ...
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