Excel - Vlookup array for multiple spreadsheets

I am attaching an example of the file using only 2 of the 12 spreadsheets.

Here is what I need:
I need to populate COLUMN Q on the tab named FPOassign.

That should be based on matching the Subscriber # in Column O tab FPOassign with the Subscriber number in Column M over on the CAP tab and resulting whatever is in column AC on the CAP tab into Column Q on the FPOassign tab.


If  the subscriber number in in both spreadsheets is the same then populate the Subscriber # in the FPOassign spreadsheet with the Internal Assigned mnemonic.  assigned to that Subscriber #.

Example:   every time that Subscriber Number 1383 matches both the CAP and FPOassign spreadsheets then in the FPOassign spreadsheet in Column Q it will show JDS for every match.  

I was doing some research and was not sure if I should use an IF statement OR a VLOOKUP .   I don't have much experience with either.
H--Excel-Question-Vlookup-or-IF-statemen
kvrogersAsked:
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.

[ fanpages ]IT Services ConsultantCommented:
Hi,

If I understand your requirement correctly, please place the formula below into cell [Q2] in the [FPOassign] worksheet:

=INDEX(CAP!AC$2:AC$37,MATCH(O2,CAP!M$2:M$37,0))

Copy cell [Q2], then paste down column [Q] until you reach cell [Q63].

Please see the attached workbook.
Q-28686498.xlsx
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
Rob HensonFinance AnalystCommented:
To answer your question specifically, you can use a VLOOKUP as suggested:

=VLOOKUP(O2,CAP!$M$1:$AC$37,17,FALSE)

This says:

Take the value from column O and find it in the first column of the range M1 to AC37 on CAP sheet. then go across to the 17th column of that range and return the value there. The false tells it to find an exact match.

Thanks
Rob H
0
kvrogersAuthor Commented:
This was the simplest way to approach this problem.  Thanks for all the help.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

[ fanpages ]IT Services ConsultantCommented:
You're very welcome.
0
Rob HensonFinance AnalystCommented:
I would not have said that the combination of INDEX & MATCH was the simplest solution as you have to understand the working of two functions whereas VLOOKUP is only one function. Each have their pros and cons.
0
kvrogersAuthor Commented:
You are probably right for as I said I'm really not familiar with either.  I had already tried the first solution and it worked.  I will be sure to try the VLOOKUP on the same report if for nothing else but to better understand the workings of both.
Thank you.
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.