I am using an array sunction consisting of a SUM( with an IF( to test a
condition on array cells and if the condition is fine I am doing a VLOOKUP(
the first argument of which has an array subtracted from a single cell
constant, and an array for the second argument. My problem is the VLOOKUP
match for the first element of the array is repeated for all the other array
elements. If experimented with different array values to figure this out
Is there a problem with VLOOKUP embedded inside an array function? I
rooted around in the help site & noticed there was a warning about using AND
or OR in array functions because of single value results I've tried an array
function using a MATCH instead of the VLOOKUP with the same arrays and MATCH
returned the correct results. I've also done an array function using the
arguments of the VLOOKUP and they also don't have this "first match" error.

How about posting a SMALL sample of your data and any formulas you're
using/trying (even if they don't work).

It sounds like you want to do lookup when there is more than one instance of
the lookup value.

--
Biff
Microsoft Excel MVP


"TomCU" wrote in message
...
I am using an array sunction consisting of a SUM( with an IF( to test a
condition on array cells and if the condition is fine I am doing a
VLOOKUP(
the first argument of which has an array subtracted from a single cell
constant, and an array for the second argument. My problem is the VLOOKUP
match for the first element of the array is repeated for all the other
array
elements. If experimented with different array values to figure this out
Is there a problem with VLOOKUP embedded inside an array function? I
rooted around in the help site & noticed there was a warning about using
AND
or OR in array functions because of single value results I've tried an
array
function using a MATCH instead of the VLOOKUP with the same arrays and
MATCH
returned the correct results. I've also done an array function using the
arguments of the VLOOKUP and they also don't have this "first match"
error.

Are you array entering a formula into multiple cells? It is likely the
case that the result array needs to transposed. You can use the
TRANPOSE function as in

=TRANSPOSE(your formula here)

Or, if you have an array of constants, switch the delimiter from a
comma to a semicolon or vice versa:

For a vertical array (one column, many rows). a,b,c separated by
semicolons:
=VLOOKUP({"a";"b";"c"},A1:B10,2,FALSE)

For a horizontal array (one row, many columns) a,b,c separated by
commas
=VLOOKUP({"a","b","c"},A1:B10,2,FALSE)

Also, double check to ensure you entered the formula with CTRL SHIFT
ENTER, just to be safe.

If this isn't it, post the formula you are using and a small set of
test data that illustrates the problem.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Mon, 15 Mar 2010 11:42:12 -0700, TomCU
wrote:

I am using an array sunction consisting of a SUM( with an IF( to test a
condition on array cells and if the condition is fine I am doing a VLOOKUP(
the first argument of which has an array subtracted from a single cell
constant, and an array for the second argument. My problem is the VLOOKUP
match for the first element of the array is repeated for all the other array
elements. If experimented with different array values to figure this out
Is there a problem with VLOOKUP embedded inside an array function? I
rooted around in the help site & noticed there was a warning about using AND
or OR in array functions because of single value results I've tried an array
function using a MATCH instead of the VLOOKUP with the same arrays and MATCH
returned the correct results. I've also done an array function using the
arguments of the VLOOKUP and they also don't have this "first match" error.