Hi!

Can't get indirect to generate an array in that form.

Not the solution you were looking for but you could always
do the monster formula like this:

=AND(VLOOKUP(B1,Sheet2!C1:J10,5,0)=F1,VLOOKUP(B1,S heet2!
C1:J10,6,0)=G1,....etc,etc)

Since B1 will always equal B1 can't you just eliminate
that from the formula?

Biff

-----Original Message-----
Bernie,

Thank you for your observation. Unfortunatelly it is just
my oversight when
putting the formula in this post as I was playing around
with the
verticality and horizontality of the arrays and
apparently have copied the
incorrect version in the rush. But you are right -
correcting it fixes the
number of results. However, those results are still
returning errors.

Thanks again, hope Harlan, Aladin or someone else could
jump in later.
KL


"Bernie Deitrick" <deitbe @ consumer dot org wrote in
message
...
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)
=INDIRECT({"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)=INDIRECT({"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





.