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