• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

How can I use a table structured reference with a vlookup() function?

I want to use a structural reference in a vlookup function. I have a table called Staff_data on a separate sheet which I want to use as the table array. I've tried typing it in as worksheet reference![staff_data], hasn't worked. Do I need to name the range like I do for creating a dynamic dropdown or what am I missing? Thank you.
0
agwalsh
Asked:
agwalsh
2 Solutions
 
NorieVBA ExpertCommented:
Try Staff_data[#All], for example.

=VLOOKUP(A1, Staff_data[#All],2,0)
0
 
ShumsDistinguished Expert - 2017Commented:
Hi,

Instead of referencing the whole table, just refer to Table Column Headers:
=VLOOKUP(B2,Table1[[ColA]:[ColC]],3,0)

Open in new window

Change the column header accordingly.
0
 
agwalshAuthor Commented:
Found the exact reference I was looking for
0
 
Rob HensonFinance AnalystCommented:
Don't forget the VLOOKUP will still look at the leftmost column of the Array, whether it is a standard range or a table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now