Odd Dynamic Range Question

[Bongard]

HI, I am trying to create lookups using dynamic ranges but am having
trouble doing either because of the format of my data. I have posted a
copy of my data format below. I have 2 reference columns (A and B) and
the data that I need to lookup is in column C. The trick is, the
values in column A are not copied all the way down, they are just a
header. I need to look up the Large, Mid/Small, and Cash values for
each portfolio. Also, the number of rows for under each portfolio can
change (see the 3rd portfolio in my dataset) Does anyone have any
ideas on how to approach this?

Help is much appreciated!
Brian


MC_CO_AGE 8.33
LARGE 17.87
MID/SMALL 76.37
[Cash] 4.66
[Unassigned] 1.10
MC_GR_Raw 8.33
LARGE 20.20
MID/SMALL 74.35
[Cash] 4.51
[Unassigned] 0.93
S&P 500 8.33
LARGE 86.68
MID/SMALL 13.32

 

[Dave Peterson]

If your data is alway like this:

MC_CO_AGE 8.33
LARGE 17.87
MID/SMALL 76.37
[Cash] 4.66
[Unassigned] 1.10

portfolio followed by 4 rows in that same exact (no variation) order, then you
could do something like:

=match(x99,sheet2!a:a,0)
Where x99 holds the portfolio name to match and sheet2 column A holds the list
of names.

This formula will give you the row of the exact match.

so if you want the row under the match, you'd just add one to that result:
=match(x99,sheet2!a:a,0)+1
That would put you to the row for Large for the matching portfolio.

Then if you wanted to bring back column C, you could use:
for Large:
=index(sheet2!c:c,match(x99,sheet2!a:a,0)+1)

for mid/small, it's +2
=index(sheet2!c:c,match(x99,sheet2!a:a,0)+2)

and so forth.

This won't work if the rows in each portfolio group aren't exactly the same.

================
If they're not, then I'd fill the empty cells with the portfolio names for that
group.

Debra Dalgleish shares a few techniques here (manual and macro):

http://contextures.com/xlDataEntry02.html
and a video
http://www.contextures.com/xlVideos01.html#FillBlanks

Then I'd use this technique to retrieve the value for matching both column A and
B.

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

HI, I am trying to create lookups using dynamic ranges but am having
trouble doing either because of the format of my data. I have posted a
copy of my data format below. I have 2 reference columns (A and B) and
the data that I need to lookup is in column C. The trick is, the
values in column A are not copied all the way down, they are just a
header. I need to look up the Large, Mid/Small, and Cash values for
each portfolio. Also, the number of rows for under each portfolio can
change (see the 3rd portfolio in my dataset) Does anyone have any
ideas on how to approach this?

Help is much appreciated!
Brian

MC_CO_AGE 8.33
LARGE 17.87
MID/SMALL 76.37
[Cash] 4.66
[Unassigned] 1.10
MC_GR_Raw 8.33
LARGE 20.20
MID/SMALL 74.35
[Cash] 4.51
[Unassigned] 0.93
S&P 500 8.33
LARGE 86.68
MID/SMALL 13.32

 

[T. Valko]

As long as each portfolio has a category for Large, Mid/Small, and Cash then
this shouldn't be a problem.

However, with that being said, I can't tell which column is which from the
posted sample data. Can you repost the sample data and use something like a
comma to delimit the columns?

 

[Bongard]

Thank you guys both for responding. The catch here is that the rows
will not automatically be the same, otherwise I think this would be a
little bit easier. The program that I'm extracting from automatically
suppresses rows if the group contains no data (automatially suppresses
the mid/small or Large rows). Also, I can pretty easily write a macro
to do this but I'm trying to avoid doing that so it can be troubleshot
by the users. I'll post a comma delimited version of my table to make
it more clear for you Biff.

Thanks again,
Brian

MC_GR_Raw,,8.33
,LARGE,20.20
,MID/SMALL,74.35
,[Cash],4.51
,[Unassigned],0.93
REIT_AGE,,8.33
,LARGE,25.10
,MID/SMALL,67.94
,[Cash],2.99
,[Unassigned],3.97
S&P 1000,,8.33
,MID/SMALL,99.98
,[Unassigned],0.02
S&P 500,,8.33
,LARGE,86.68
,MID/SMALL,13.32
S&P Midcap,,8.33
,MID/SMALL,100.00
S&P Smallcap,,8.33
,MID/SMALL,99.93
,[Unassigned],0.07
SC_CO_AGE,,8.33
,LARGE,0.72
,MID/SMALL,94.34
,[Cash],4.34
,[Unassigned],0.59

 

[T. Valko]

Ok, that really complicates things since not every portfolio has the same
categories.

In this case, and, if you want a formula solution I would do as Dave
Peterson suggested, fill the empty cells in column A with the portfolio
name. Then it becomes fairly simple.

 

[Bongard]

I went the direction of writing a quick macro to import the data and
copy down the cells in column A. I then created some lookups with the
format If(columnA="Ptfl",If(columnB="Size",columnC))). This seems to
be working just fine.

thanks for your help,
Brian

 

[T. Valko]

Good deal. Thanks for the feedback!