Vlookup/Match Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all, hope you can help!

Here is my dilema. i have a raw data that is formatted like this

group type value1 value2

group a new 1 1
group a old 1 1
group b new 1 1
group b old 1 1

I need to be able to pull data horizontally on another datasheet first by
type and then by group. Format looks like this:

Value1 for new Value2 for new Value1 for
old Value2 old
group name

I have no clue where to start. I have done vlookups and matches before, but
not for multiple columns. Thought of using an If statement with an and, but
can't conceptualize how it would look.

Please help!
 
group type Value1 value2 <==Row 1
group a new 1 2
group a old 3 4
group b new 5 6
group b old 7 8

Results:

Group Value 1 Value 2 Value 1 value2 '<=== row 8
Group a 1 2 3 4
Group b 5 6 7 8

new Value 1 (B9)

=SUMPRODUCT(--($A$2:$A$5=$A8),--($B$2:$B$5="new"),$C$2:$C$5)

new Value 2 (C9)

=SUMPRODUCT(--($A$2:$A$5=$A8),--($B$2:$B$5="new"),$C$2:$C$5)

Copy above to D9 & E9

Replace "new" with "old" for Old values 1 & 2

Copy B9:E9 down

HTH
 
Back
Top