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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was the simplest way to approach this problem. Thanks for all the help.
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
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.
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.
Thank you.
=VLOOKUP(O2,CAP!$M$1:$AC$3
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