hrlngrv,

Thanks a bunch - that works. Now out of curiosity: what should I be doing if
I have numbers or a mix of numbers and text?

Apreciate your help,
KL

"hrlngrv - ExcelForums.com" wrote in
message ...
KL wrote...
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},FALS E)
=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!,#VALUE!}
..

You've already been told to use a horizontal array of column indices
as 3rd argument to VLOOKUP, so {1,5,6,7,8}. Alternatively, you could
use a vertical array of textrefs to INDIRECT, so
{"B1";"F1";"G1";"H1";"J1"}. Either will make the two resulting array
the same shape/orientation, and will reduce the result of the
comparison to either a simple horizontal or veritcal array.

As for the #VALUE! results, INDIRECT when passed an array first
argument returns somehting that works like an array of range
references. In some situations those will appear to work as operands,
in other situations (such as array formulas) they won't. Since it
appears these cells will evaluate to text, wrap the INDIRECT call
inside T(). So try the formula

=VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0)
=T(INDIRECT({"B1";"F1";"G1";"H1";"J1"}))
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!