Searching through an array once target area is found by way of vlookup
Posted by dan spald on September 05, 2001 7:05 AM
I have a list that changes length and is grouped by portfolio name. I want to write a statement that first searches for portfolio name, sets an array to search in(reletive reference, beginning with port name,A1:h10 for a 10 fund portfolio) and then by a specific fund held within. I have been trying something like the following but have found an impass =VLOOKUP("SVUL PORT",rorytd!$A:$A,VLOOKUP("MMKT",A1:F10,5,),FALSE)
This will be used to check monthly rates of return in 40 portfolios consisting of some combination of 80 funds with varying charges.
Thank you for the look over,
Dan
Posted by IML on September 05, 2001 10:04 AM
I assumed portfolio name in A, Security in b, and your value in C (rows 1 - 100)
Maybe someone can pitch in with a good solution to create some dynamic ranges. But for this to work, your list would need to sorted and you would manually need to name the ranges as follows.
Lets say A1:a50 is PortA. Name cells B1:B50 "PortA" via the name box. Repeat for all portfolios.
I'm further assuming you will enter portfolio in E1 and security in e2.
Your lookup formula would be:
=VLOOKUP(E2,INDIRECT(E1),2,FALSE)
Alternatively, you may want to try the formula
=SUM((A1:A100=E1)*(B1:B100=E2)*(C1:C100))
(same assumptions apply). You must hit enter while control and shift are depressed to have this work. Brackets will appear around the formula if you do it right.
Good luck.
Posted by IML on September 05, 2001 10:07 AM
oops
For naming your ranges if PortA is in A1:A50, name B1:C50 (not B50) "PortA" via the name box. You may always want to consider data validation so users can only enter the names as you have defined them.
