Sumproduct with vlookup (or any other solution that will help)

[krish2503]

Hi. Would appreciate any help on this.
I am attaching a spreadsheet which should explain what I am trying to get at. I have a table of data which is a download from a dw (I have picked 6 rows but there are maybe 3500 rows in the actual download). As shown in the output needed section, I need to to pick data from Table 2 to give me 3 numbers i.e. Month, Quarter to Date and Year to Date based on the month picked, for a country e.g. France. I built Table 1 to give me the 0,1 values based on which month is picked. In the table shown, the month picked is June and accordingly the 0,1 is worked for Qtd and YTD as well.

I can use the =sumproduct(b3:m3,b12:m12) for getting the result for France (row 12) but the problem is that since the data is based on a data download, France can move up or down on the download and my static formula would not work...and hence was looking at vlookup to help me pick France, but again vlookup, I believe, doesn't work with arrays...so am stumped. Could someone help? Maybe I am going in the wrong direction altogether?? HELP

Krish

[Pepe Le Mokko]

In your example in B19 enter

Please Login or Register  to view this content.

and drag right

[Ron Coderre]

Using your posted workbook...
these regular formulas return the country totals for MTH, QTD, and YTD based on the flags in Table1

Please Login or Register  to view this content.

Is that something you can work with?

[krish2503]

Brilliant... thanks to Pepe and Ron..both the methodologies work!! Just made my day!!

Does it mean that sumproduct and vlookup can never work together :-)??

[Pepe Le Mokko]

They could probably, but I'm personnaly not a big fan of Vlookup as INDEX/MATCH is a much more flexible ( and faster ) solution