• 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.

how to make a Vlookup work with a named column of data on a different tab

S

Sophia

New Member
Hi There,
My usual Formula is:
=vlookup(A2,Table,26,false)
This works if the data is in the same sheet or if the data is in a different tab.
What I need to know is if I take all of column 26 (which is Z) and name it Apricot how does that formula work.

My problem right now is my formula becomes invalid every time I add a column in the main data tab, but if I could find a way for my vlookup to pull using the name (for example apricot) then it wouldn’t matter how many columns I add, move or delete and the rest of the data won’t get messed up.

Theoretically if you use the above formula it should be something like:
=vlookup(A2,Table,Apricot,false)

But every time I do that it gives me an error.
I’ve googled it quite a bit and aren’t getting any luck either so I figured I’d ask you.
Do you possibly have a workaround or solution?
 
In your example, Apricot is the named range Z:Z.
In =vlookup(A2,Table,Z:Z,false) is not valid, since Z:Z is not a number.

COLUMN(Z:Z) is 26, so either change the vlookup formula or the named range to include the COLUMN.
 
Two standard ways are
1. To continue using VLOOKUP but to determine the column index by matching the column header against the desired field name
2. Give up VLOOKUP and use INDEX / MATCH which is capable of addressing your 'apricots' correctly!
 
You must log in or register to reply here.
Back
Top