Simple array compare not working

[PeteJ]

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

 

[Pete_UK]

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

Pete

 

[PeteJ]

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.

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

Pete

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]

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.)

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.

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

Pete

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

 

[Rick Rothstein \(MVP - VB\)]

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

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.

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

Pete

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

 

[PeteJ]

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 before:

= 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?

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

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.

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

Pete

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]

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.

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 before:

= 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?

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

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.

:

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

Pete

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

 

[PeteJ]

Awesome, thanks. I had taken that formula directly from another post, but it
does make sense.

- 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.

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 before:

= 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?

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


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.

:

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

Pete

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