Hello, maybe someone can see something obvious here.

I've got the following compare as part of a MATCH function, and it always
returns false. It doesn't seem to be stepping past the first row of the
column A. I am entering this as an array formula (ctrl-shift-enter). How come
it's not going past the first row?

{=('[File1.xls]SW'!A2:A100=A2)}

I get the same behavior using

{=('[File1.xls]SW'!$A:$A=A2)}

There is definately a value in column A that matches A2 of this sheet. It
works with a VLOOKUP.

Thanks,

- Pete

You say it is part of a MATCH function - can you post a copy of the complete
formula that you are using?

Pete

"PeteJ" wrote in message
...
Hello, maybe someone can see something obvious here.

I've got the following compare as part of a MATCH function, and it always
returns false. It doesn't seem to be stepping past the first row of the
column A. I am entering this as an array formula (ctrl-shift-enter). How
come
it's not going past the first row?

{=('[File1.xls]SW'!A2:A100=A2)}

I get the same behavior using

{=('[File1.xls]SW'!$A:$A=A2)}

There is definately a value in column A that matches A2 of this sheet. It
works with a VLOOKUP.

Thanks,

- Pete

Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with
multiple columns to compare without editing the original file. I got this
hint from the discussion boards, and am trying to apply it.

{=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) *
('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))}

In the Match, I am multiplying 3 results to see if they all return "TRUE".
But they are all returning "FALSE". Actually, the whole formula returns
#NUM!, but stepping through it's because the array compares are returning
"FALSE" all the time.

The following works just fine:

=VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE)

But of course only takes into consideration the A2 cell.

"Pete_UK" wrote:

You say it is part of a MATCH function - can you post a copy of the complete
formula that you are using?

Pete

"PeteJ" wrote in message
...
Hello, maybe someone can see something obvious here.

I've got the following compare as part of a MATCH function, and it always
returns false. It doesn't seem to be stepping past the first row of the
column A. I am entering this as an array formula (ctrl-shift-enter). How
come
it's not going past the first row?

{=('[File1.xls]SW'!A2:A100=A2)}

I get the same behavior using

{=('[File1.xls]SW'!$A:$A=A2)}

There is definately a value in column A that matches A2 of this sheet. It
works with a VLOOKUP.

Thanks,

- Pete

Unless you're using xl2007, you can't use the whole column in your array
formula.

(You are using ctrl-shift-enter to enter the formula in the cell, right?)

And you may want to limit the range to what you expect (plus a little bit
more). I've seen some formulas that try to use lots of rows (or complete
columns) cause excel to yell about not being able to complete the task with
available resources. (That may be when the "sending" workbook is closed,
though.)


PeteJ wrote:

Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with
multiple columns to compare without editing the original file. I got this
hint from the discussion boards, and am trying to apply it.

{=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) *
('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))}

In the Match, I am multiplying 3 results to see if they all return "TRUE".
But they are all returning "FALSE". Actually, the whole formula returns
#NUM!, but stepping through it's because the array compares are returning
"FALSE" all the time.

The following works just fine:

=VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE)

But of course only takes into consideration the A2 cell.

"Pete_UK" wrote:

You say it is part of a MATCH function - can you post a copy of the complete
formula that you are using?

Pete

"PeteJ" wrote in message
...
Hello, maybe someone can see something obvious here.

I've got the following compare as part of a MATCH function, and it always
returns false. It doesn't seem to be stepping past the first row of the
column A. I am entering this as an array formula (ctrl-shift-enter). How
come
it's not going past the first row?

{=('[File1.xls]SW'!A2:A100=A2)}

I get the same behavior using

{=('[File1.xls]SW'!$A:$A=A2)}

There is definately a value in column A that matches A2 of this sheet. It
works with a VLOOKUP.

Thanks,

- Pete




--

Dave Peterson

To the best of my knowledge, you cannot use whole column references in array
formulas... try $H1:$Hnnn (where nnn is the largest numbered row you will
need... I'm told it is more efficient to restrict the size of the range
where possible), $A1:$Annn, etc.

Rick


"PeteJ" wrote in message
...
Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with
multiple columns to compare without editing the original file. I got this
hint from the discussion boards, and am trying to apply it.

{=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) *
('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))}

In the Match, I am multiplying 3 results to see if they all return "TRUE".
But they are all returning "FALSE". Actually, the whole formula returns
#NUM!, but stepping through it's because the array compares are returning
"FALSE" all the time.

The following works just fine:

=VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE)

But of course only takes into consideration the A2 cell.

"Pete_UK" wrote:

You say it is part of a MATCH function - can you post a copy of the
complete
formula that you are using?

Pete

"PeteJ" wrote in message
...
Hello, maybe someone can see something obvious here.

I've got the following compare as part of a MATCH function, and it
always
returns false. It doesn't seem to be stepping past the first row of the
column A. I am entering this as an array formula (ctrl-shift-enter).
How
come
it's not going past the first row?

{=('[File1.xls]SW'!A2:A100=A2)}

I get the same behavior using

{=('[File1.xls]SW'!$A:$A=A2)}

There is definately a value in column A that matches A2 of this sheet.
It
works with a VLOOKUP.

Thanks,

- Pete

Thanks. I've changed the reference to be limited, and the result does change,
but I now get back #N/A instead of #NUM!. I've tried to simplify the formula
as much as possible to debug it. It's now:

{=INDEX(I2:I20, MATCH(1, (E2:E20=A2), 0))}

Yes, entered with ctrl-shift-enter.

Stepping through the calculation, it gets the value of A2 correctly, then
expands out E2:E20 correctly with the values from those cells. It then
changes this to a list of values of "False" and "True", with the "True" value
being 3rd in the list, properly cooresponding the the 3rd value in the list
that matches.

This is where things go wrong, with the next evaluation changing the whole
MATCH statement to "#N/A".

Here's the evaluation right befo

= INDEX($I$2:$I:$20, MATCH(1,{FALSE, FALSE, TRUE, FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
FALSE, FALSE},0))

Any ideas?


"Rick Rothstein (MVP - VB)" wrote:

To the best of my knowledge, you cannot use whole column references in array
formulas... try $H1:$Hnnn (where nnn is the largest numbered row you will
need... I'm told it is more efficient to restrict the size of the range
where possible), $A1:$Annn, etc.

Rick


"PeteJ" wrote in message
...
Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with
multiple columns to compare without editing the original file. I got this
hint from the discussion boards, and am trying to apply it.

{=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) *
('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))}

In the Match, I am multiplying 3 results to see if they all return "TRUE".
But they are all returning "FALSE". Actually, the whole formula returns
#NUM!, but stepping through it's because the array compares are returning
"FALSE" all the time.

The following works just fine:

=VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE)

But of course only takes into consideration the A2 cell.

"Pete_UK" wrote:

You say it is part of a MATCH function - can you post a copy of the
complete
formula that you are using?

Pete

"PeteJ" wrote in message
...
Hello, maybe someone can see something obvious here.

I've got the following compare as part of a MATCH function, and it
always
returns false. It doesn't seem to be stepping past the first row of the
column A. I am entering this as an array formula (ctrl-shift-enter).
How
come
it's not going past the first row?

{=('[File1.xls]SW'!A2:A100=A2)}

I get the same behavior using

{=('[File1.xls]SW'!$A:$A=A2)}

There is definately a value in column A that matches A2 of this sheet.
It
works with a VLOOKUP.

Thanks,

- Pete

This array formula:
=INDEX(I2:I20, MATCH(1, (E2:E20=A2), 0))
should be:
=INDEX(I2:I20, MATCH(True, (E2:E20=A2), 0))

With your other formula, you're multiplying true/falses to get 1's and 0's.




PeteJ wrote:

Thanks. I've changed the reference to be limited, and the result does change,
but I now get back #N/A instead of #NUM!. I've tried to simplify the formula
as much as possible to debug it. It's now:

{=INDEX(I2:I20, MATCH(1, (E2:E20=A2), 0))}

Yes, entered with ctrl-shift-enter.

Stepping through the calculation, it gets the value of A2 correctly, then
expands out E2:E20 correctly with the values from those cells. It then
changes this to a list of values of "False" and "True", with the "True" value
being 3rd in the list, properly cooresponding the the 3rd value in the list
that matches.

This is where things go wrong, with the next evaluation changing the whole
MATCH statement to "#N/A".

Here's the evaluation right befo

= INDEX($I$2:$I:$20, MATCH(1,{FALSE, FALSE, TRUE, FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
FALSE, FALSE},0))

Any ideas?

"Rick Rothstein (MVP - VB)" wrote:

To the best of my knowledge, you cannot use whole column references in array
formulas... try $H1:$Hnnn (where nnn is the largest numbered row you will
need... I'm told it is more efficient to restrict the size of the range
where possible), $A1:$Annn, etc.

Rick


"PeteJ" wrote in message
...
Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with
multiple columns to compare without editing the original file. I got this
hint from the discussion boards, and am trying to apply it.

{=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) *
('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))}

In the Match, I am multiplying 3 results to see if they all return "TRUE".
But they are all returning "FALSE". Actually, the whole formula returns
#NUM!, but stepping through it's because the array compares are returning
"FALSE" all the time.

The following works just fine:

=VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE)

But of course only takes into consideration the A2 cell.

"Pete_UK" wrote:

You say it is part of a MATCH function - can you post a copy of the
complete
formula that you are using?

Pete

"PeteJ" wrote in message
...
Hello, maybe someone can see something obvious here.

I've got the following compare as part of a MATCH function, and it
always
returns false. It doesn't seem to be stepping past the first row of the
column A. I am entering this as an array formula (ctrl-shift-enter).
How
come
it's not going past the first row?

{=('[File1.xls]SW'!A2:A100=A2)}

I get the same behavior using

{=('[File1.xls]SW'!$A:$A=A2)}

There is definately a value in column A that matches A2 of this sheet.
It
works with a VLOOKUP.

Thanks,

- Pete






--

Dave Peterson