How to report a bug

[PJFry]

I have found a bug using the Match function in Excel 2007 and I want to see
if it has already been reported. How can I do that?

 

[Gary''s Student]

Post a description of you bug here and get some feedback

 

[Niek Otten]

Post it here

 

[T. Valko]

How about posting a description/explanation.

 

[PJFry]

The formula starts off like so:
=VLOOKUP($A3,pvt90DNet'!$A:$AZ,MATCH('pvt90DNet'!B$2,'pvtCnt90'!$2:$2,0)

But when I copy it down the range, the lookup array for the match loses its
fixed reference, like so:
=VLOOKUP($A3,pvt90DNet'!$A:$AZ,MATCH('pvt90DNet'!B$2,'pvtCnt90'!2:2,0)

If I go back and re-fix the reference and copy it down, no issues. If make
a change to the formula and copy it down the range, the references are no
longer fixed again.

 

[Gary''s Student]

Sadly, I cannot replicate your problem. If I put your formula in a cell and
copy downwards, I get:

=VLOOKUP($A3,pvt90DNet!$A:$AZ,MATCH(pvt90DNet!B$2,pvtCnt90!$2:$2,0))
=VLOOKUP($A4,pvt90DNet!$A:$AZ,MATCH(pvt90DNet!B$2,pvtCnt90!$2:$2,0))
=VLOOKUP($A5,pvt90DNet!$A:$AZ,MATCH(pvt90DNet!B$2,pvtCnt90!$2:$2,0))
=VLOOKUP($A6,pvt90DNet!$A:$AZ,MATCH(pvt90DNet!B$2,pvtCnt90!$2:$2,0))
=VLOOKUP($A7,pvt90DNet!$A:$AZ,MATCH(pvt90DNet!B$2,pvtCnt90!$2:$2,0))
=VLOOKUP($A8,pvt90DNet!$A:$AZ,MATCH(pvt90DNet!B$2,pvtCnt90!$2:$2,0))

The $A3 adjusts as it should. $2:$2 retains its integrity.
Same behavior in 2003 & 2007

 

[T. Valko]

=VLOOKUP($A3,pvt90DNet'!$A:$AZ,MATCH('pvt90DNet'!B$2,'pvtCnt90'!$2:$2,0)

Your formula as posted is incomplete.

I am unable to reproduce that behavior.

In Excel 2007...

=VLOOKUP($A3,'Sheet 2'!$A:$H,MATCH(B$2,'Sheet 2'!$2:$2,0),0)

I entered that formula in a cell and drag copied down and it worked as
expected. The reference to Sheet2!$2:$2 remained absolute.

I entered the formula in a cell then did Copy>Paste in several other cells
and again, it worked as expected.

 

[Bernard Liengme]

I find that in Excel 2003 and Excel 2007
=VLOOKUP($A3,Sheet4!$A:$AZ,MATCH(Sheet4!B$2,Sheet4!2:$2,0))
becomes
=VLOOKUP($A4,Sheet4!$A:$AZ,MATCH(Sheet4!B$2,Sheet4!$2:3,0))
Note the $ before the $2:3

But if you start with $2:$2 it stays that way