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.
IO_DorkAsked:
Who is Participating?
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.

ProfessorJimJamCommented:
in this case, you need the match function to join with vlookup
0

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?
0
ProfessorJimJamCommented:
see attached example
Book1.xlsx
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

IO_DorkAuthor Commented:
wow, more simple than I thought, Thanks!
0
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.
0
ProfessorJimJamCommented:
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.
0
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.
0
IO_DorkAuthor Commented:
that's where it must be going wrong....my column reference is differ in length than the actual lookup array table.

=VLOOKUP($B7,'fdic_financials'!$A$3:$Z$6600,MATCH(U$1,'fdic_financials'!$M$2:$Y$2,0),FALSE)
0
ProfessorJimJamCommented:
change it to this and see if it works.

=VLOOKUP($B7,'fdic_financials'!$A$3:$Z$6600,MATCH(U$1,'fdic_financials'!$A$2:$Z$2,0),FALSE)
0
IO_DorkAuthor Commented:
yep, that worked, Thanks!
0
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.

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.