Link to home
Start Free TrialLog in
Avatar of kvrogers

asked on

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.
Avatar of [ fanpages ]
[ fanpages ]

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of kvrogers


This was the simplest way to approach this problem.  Thanks for all the help.
You're very welcome.
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.