gauntletxg
Well-known Member
- Joined
- Jul 15, 2008
- Messages
- 636
I currently have this VLOOKUP function:
=VLOOKUP($E$3,INDIRECT("AP170FY"&B:B&".xls"&"!"&PROPER(LEFT(D:D,FIND(" ",D:D)-1))&$E$10),3,FALSE))
I am using it to look up inventory numbers from an external workbook, based on supplier ID and month. This works great.
The problem is sometimes multiple suppliers are rolled up into one. So to get a sum for those instances, I currently modify the formula to something like this:
=VLOOKUP($E$3,INDIRECT("AP170FY"&B:B&".xls"&"!"&PROPER(LEFT(D:D,FIND(" ",D:D)-1))&$E$10),3,FALSE))+VLOOKUP($E$4,INDIRECT("AP170FY"&B:B&".xls"&"!"&PROPER(LEFT(D:D,FIND(" ",D:D)-1))&$E$10),3,FALSE))
Basically just repeating the formula and changing the ID I need to look up. But there has to be an easier way to do this. This method gets lengthy, inefficient, and is a pain to debug. Ideally I'd like to select a range of say 5 cells that each contain a unique ID, and tell the formula to look all of them up and sum the results.
How would I go about doing this?
Thanks.
=VLOOKUP($E$3,INDIRECT("AP170FY"&B:B&".xls"&"!"&PROPER(LEFT(D:D,FIND(" ",D:D)-1))&$E$10),3,FALSE))
I am using it to look up inventory numbers from an external workbook, based on supplier ID and month. This works great.
The problem is sometimes multiple suppliers are rolled up into one. So to get a sum for those instances, I currently modify the formula to something like this:
=VLOOKUP($E$3,INDIRECT("AP170FY"&B:B&".xls"&"!"&PROPER(LEFT(D:D,FIND(" ",D:D)-1))&$E$10),3,FALSE))+VLOOKUP($E$4,INDIRECT("AP170FY"&B:B&".xls"&"!"&PROPER(LEFT(D:D,FIND(" ",D:D)-1))&$E$10),3,FALSE))
Basically just repeating the formula and changing the ID I need to look up. But there has to be an easier way to do this. This method gets lengthy, inefficient, and is a pain to debug. Ideally I'd like to select a range of say 5 cells that each contain a unique ID, and tell the formula to look all of them up and sum the results.
How would I go about doing this?
Thanks.