Excel Beta 2TR Bug in Vlookup?

D

Deb Plosk77

Hi,

In copying this formula:

=VLOOKUP(E$42,IData!$E$3:$AY$65536,35,FALSE)

from one column to another, regular copy and paste, it treats e$42 as if it
is $e$42. If I copy and paste special, formulas and number formats, it will
update the column reference correctly.

Does anyone know if this is a known bug? Do you have this problem as well?
Not sure where to post possible problems so thought I'd try here as well as
mailing MS directly.

Thank you,
Deb
 
D

Deb Plosk77

Hi Dave,

Thanks for your response. I'm at home (I have the Beta at work) and tested
your idea on 2003. I have calculation off, but it still updates the formula.
The value is not updated, but the formula reference correctly updates. Then
when I hit calculate it calculates correctly.

My issue is that in 2007 the formula itself is not updating, copying it from
column E to D it still says e$42...
 
J

Jim Rech

I entered your formula in Excel 2007 and did a regular copy and paste one
column to the right. The E$3 became F$3 as it should.

Incidentally your formula could be written to compute more efficiently,
particularly if you are going to copy it many times. The problem is that
your formula creates a dependency on 47 columns when all it really depends
on need is 2 (column E and column AM, the 35th):

=INDEX($AM$3:$AM$65536,MATCH(E$42,IData!$E$3:$E65536,FALSE))

If you do not have data going to row 65536 you might pick up efficiency by
referencing the rows that will actually have data.
 
D

Deb Plosk77

Thanks Jim. Any other ideas as to what might be causing this? I've
replicated this several times, and just again now. Copying and pasting to
the left or the right it does not update the reference, whether in manual or
automatic calculation. The worksheet is unprotected. Still seems like it
must be a bug unless I have some funky setting set . . .

I'm working off someone else's code and am gradually replacing the VLookups
for efficiency, as you mention ... I think the Index and Match idea is a
good one for this sheet, thank you. (For some others I've been using Match
and Offset). As we use this same spreadsheet every year with different
amounts of data, referencing the exact # of rows where data is present has
caused problems.

Deb
 
D

Deb Plosk77

By the way I like this formula and tried it and it works great ... except to
copy it it doesn't update the column reference either (e.g., J$41 below, I
manually edited to have J)!

here it is now in column J:
=INDEX(IData!$AI3:$AI4000,MATCH(J$41,IData!$E$3:$E$65536,0))

here it is when I copy from column J to K:
=INDEX(IData!$AI3:$AI4000,MATCH(J$41,IData!$E$3:$E$65536,0))

So it's not just vlookup ...
 
Top