Match two columns and return a third one

[solexcel81]

Hi,

I am looking for a way to match a couple of columns, as vlookup is not useful in this case.

for example: I have 3 columns with names of sales people, months and their units sold, something like this: (note: they can sell more than once a month)

, and I want to regroup their sales in a data table that would look something like this:

I am battling with index and match but I haven't been able to figure out the right combination. Any ideas of how you would do it?

Thanks!

 

[missgari]

How about a pivot table? I don't know if maybe you have other criteria that make it impossible for you to use that method..

 

[Jonmo1]

Welcome to the board...

So long as the value to return is numeric (column J - Units Sold), then this seems appropriate..

In M6
=SUMPRODUCT(--($H$6:$H$17=$L6),--($I$6:$I$17=M$5),$J$6:$J$17)

Then fill/copy down and right.

Hope that helps.

 

[PV3]

You could create a "unique ID" in a column to the left, then in your table, make a sumif. For example, M6 could read =sumif(columnwhereyouhavetheuniqueid's,"JakeJan",ColumnJ)

 

[T. Valko]

Hi,

I am looking for a way to match a couple of columns, as vlookup is not useful in this case.

for example: I have 3 columns with names of sales people, months and their units sold, something like this: (note: they can sell more than once a month)

, and I want to regroup their sales in a data table that would look something like this:

I am battling with index and match but I haven't been able to figure out the right combination. Any ideas of how you would do it?

Thanks!

What version of Excel are you using?

 

[solexcel81]

Wow, thanks for these fast replies, I will try both and see how it turns out ...

I am using 2010, or at least trying

 

[Jonmo1]

In 2007 or higher you can use SUMIFS instead of the SUMPRODUCT I posted...

=SUMIFS($J$6:$J$17,$H$6:$H$17,$L6,$I$6:$I$17,M$5)

 

[T. Valko]

I am using 2010, or at least trying

Try this...

Entered in M6:

=SUMIFS($J$7:$J$17,$H$7:$H$17,$L6,$I$7:$I$17,M$5)

Copy across to R6 then down to M10:R10.

 

[solexcel81]

SUMIFS and SUMPRODUCT work pretty good and its simple enough, I was making my life more complicated with index and match ...

Thanks!