Excel - Vlookup array for multiple spreadsheets

kvrogers used Ask the Experts™
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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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


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

Please see the attached workbook.
Rob HensonFinance Analyst

To answer your question specifically, you can use a VLOOKUP as suggested:


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.

Rob H


This was the simplest way to approach this problem.  Thanks for all the help.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

You're very welcome.
Rob HensonFinance Analyst

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.


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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial