• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Freeze column reference in dynamic table for VLOOKUP

D

DashboardNovice

Member
I have a VLOOKUP in a dynamic range:

=IFNA(VLOOKUP([@Vendor],Lookup!$A$52:$H$57,COLUMN(B5000),0),"")

I want the [Vendor] field to be locked with $. When I copy across, the next column is substituted for [Vendor] which I do not want. I want to freeze the [Vendor] field.

Is this possible when the field you are referencing is part of a dynamic range?
 
For a single cell, try using [@[Field]:[Field]], so in this case, [@[Vendor]:[Vendor]]. Remove the @ for an entire column reference.
 
Alternatively, you can also just Copy Paste instead of dragging. This will keep the structural refs intact while changing relative refs as you would expect.
 
You must log in or register to reply here.
Back
Top