vlookup formula to dynamically reference columns

I need my vlookup() formula to look for a column by matching it instead of the basic vlookup() formula that has a static reference... column "1", "2", etc.

For example, if in the table lookup, the desired column with header of "asset" changes its column position, I want my vlookup() to be able to find that column instead of expecting it to always be col. "1".

What is the best way to accomplish this?...nest another hlookup() inside the vlookup() where the column reference goes? or does some kind of index match work better, or some other solution?

Keep in mind I have 6500 rows in both the lookup table array and the lookup value table, and 12 columns that will be used in lookup functions.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
in this case, you need the match function to join with vlookup

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IO_DorkAuthor Commented:
you mean just the match or the index-match combo with vlookup?
ProfessorJimJamMicrosoft Excel ExpertCommented:
see attached example
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

IO_DorkAuthor Commented:
wow, more simple than I thought, Thanks!
IO_DorkAuthor Commented:
does all the lookup have to be in the same sheet?...I am getting N/A when I am trying to lookup the values that are on another table in another sheet.
ProfessorJimJamMicrosoft Excel ExpertCommented:
not really, it does not matter where the sheet be,

can you upload a dummy example?  i would like see where are u making mistake in formula.

one thing,  your match horizontal range for match should not be less or more that the dimension of the lookup range.
IO_DorkAuthor Commented:
actually, I got it pulling data but its pulling from wrong column. not sure why its doing this since I have everything referencing correctly. I'll have to look at it longer to see where I am going wrong.
IO_DorkAuthor Commented:
that's where it must be going wrong....my column reference is differ in length than the actual lookup array table.

ProfessorJimJamMicrosoft Excel ExpertCommented:
change it to this and see if it works.

IO_DorkAuthor Commented:
yep, that worked, Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.