Avatar of kvrogers
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.
H--Excel-Question-Vlookup-or-IF-statemen
Microsoft ExcelSpreadsheets

Avatar of undefined
Last Comment
kvrogers

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
[ fanpages ]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

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
kvrogers

ASKER
This was the simplest way to approach this problem.  Thanks for all the help.
[ fanpages ]

You're very welcome.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rob Henson

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.
kvrogers

ASKER
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.