Excel best practice

[PTP86]

Q1
---
Pretend in J3 you've got the formula =VLookup(A3, NamedRange,4,FALSE) and you have that formula all the way down column J
Is there a right/wrong in whether you choose to write it as =VLookup(A3, NamedRange,4,FALSE) or =VLookup($A3, NamedRange,4,FALSE)

I know that the $ makes no difference to the result initially because of how the formula isn't getting copied across, just down.
But I was wondering whether there is any reason why an "expert" would still put a $ in there?



Q2
---
Pretend you've got a large spreadsheet with potentially lots of large tables, lots of lookups/countifs/sumifs.
Pretend the answer to the formula you've got in Column J won't change unless the answer of the formula in Column B has changed
Is it any more efficient to write J3 = If(B3=B2,J2,VLookup(A3,NamedRange,4,FALSE)
instead of J3 = VLookup(A3,NamedRange,4,FALSE)

i.e. Does it save Excel from thinking it needs to do another VLookup and so speed it up?


Thanks

 

[RoryA]

Q1 Not really.
Q2 Yes. It's much more efficient to avoid doing a lookup if you don't need it.